[Home] [Help]
40: l_proc varchar2(72) := g_package||'set_security_group_id';
41: --
42: begin
43: --
44: hr_utility.set_location('Entering:'|| l_proc, 10);
45: --
46: -- Ensure that all the mandatory parameter are not null
47: --
48: hr_api.mandatory_arg_error
73: (p_security_group_id => l_security_group_id
74: );
75: --
76: end if;
77: hr_utility.set_location(' Leaving:'|| l_proc, 20);
78: --
79: end set_security_group_id;
80: --
81: -- ---------------------------------------------------------------------------
104: l_proc varchar2(72) := g_package||'return_legislation_code';
105: --
106: Begin
107: --
108: hr_utility.set_location('Entering:'|| l_proc, 10);
109: --
110: -- Ensure that all the mandatory parameter are not null
111: --
112: hr_api.mandatory_arg_error
122: -- call to this function. Just return the value in the global
123: -- variable.
124: --
125: l_legislation_code := irc_iof_bus.g_legislation_code;
126: hr_utility.set_location(l_proc, 20);
127: else
128: --
129: -- The ID is different to the last call to this function
130: -- or this is the first call to this function.
139: close csr_leg_code;
140: fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
141: fnd_message.raise_error;
142: end if;
143: hr_utility.set_location(l_proc,30);
144: --
145: -- Set the global variables so the values are
146: -- available for the next call to this function.
147: --
148: close csr_leg_code;
149: irc_iof_bus.g_offer_id := p_offer_id;
150: irc_iof_bus.g_legislation_code := l_legislation_code;
151: end if;
152: hr_utility.set_location(' Leaving:'|| l_proc, 40);
153: return l_legislation_code;
154: end return_legislation_code;
155: --
156: -- ----------------------------------------------------------------------------
187: --
188: l_proc varchar2(72) := g_package || 'chk_df';
189: --
190: begin
191: hr_utility.set_location('Entering:'||l_proc,10);
192: --
193: if ((p_rec.offer_id is not null) and (
194: nvl(irc_iof_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
195: nvl(p_rec.attribute_category, hr_api.g_varchar2) or
327: );
328: null;
329: end if;
330: --
331: hr_utility.set_location(' Leaving:'||l_proc,20);
332: end chk_df;
333: --
334: -- ----------------------------------------------------------------------------
335: -- |-----------------------< chk_non_updateable_args >------------------------|
469: between effective_start_date
470: and effective_end_date;
471: --
472: Begin
473: hr_utility.set_location('Entering:'||l_proc,10);
474: --
475: hr_api.mandatory_arg_error
476: (p_api_name => l_proc
477: ,p_argument => 'effective_date'
486: --
487: open csr_applicant_assignment_id;
488: fetch csr_applicant_assignment_id into l_applicant_assignment_id;
489: --
490: hr_utility.set_location(l_proc,20);
491: if (csr_applicant_assignment_id%notfound)
492: then
493: close csr_applicant_assignment_id;
494: fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
495: fnd_message.raise_error;
496: end if;
497: close csr_applicant_assignment_id;
498: --
499: hr_utility.set_location(' Leaving:'||l_proc,30);
500: exception
501: when app_exception.application_exception then
502: if hr_multi_message.exception_add
503: (p_associated_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
501: when app_exception.application_exception then
502: if hr_multi_message.exception_add
503: (p_associated_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
504: ) then
505: hr_utility.set_location(' Leaving:'|| l_proc, 40);
506: raise;
507: end if;
508: hr_utility.set_location(' Leaving:'|| l_proc, 50);
509: End chk_applicant_assignment_id;
504: ) then
505: hr_utility.set_location(' Leaving:'|| l_proc, 40);
506: raise;
507: end if;
508: hr_utility.set_location(' Leaving:'|| l_proc, 50);
509: End chk_applicant_assignment_id;
510: --
511: -- ----------------------------------------------------------------------------
512: -- |-----------------------< chk_offer_assignment_id >--------------------|
550: from irc_offers
551: where offer_assignment_id = p_offer_assignment_id;
552: --
553: Begin
554: hr_utility.set_location('Entering:'||l_proc,10);
555: --
556: if hr_multi_message.no_exclusive_error(
557: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
558: ) then
568: --
569: if (csr_offer_assignment_id%notfound)
570: then
571: --
572: hr_utility.set_location(l_proc,20);
573: --
574: close csr_offer_assignment_id;
575: fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
576: fnd_message.raise_error;
585:
586: if (csr_offer_assigment_exists%found)
587: then
588: --
589: hr_utility.set_location(l_proc,25);
590: --
591: close csr_offer_assigment_exists;
592: fnd_message.set_name('PER','IRC_412348_OFR_ASNMT_EXISTS');
593: fnd_message.raise_error;
594: end if;
595: close csr_offer_assigment_exists;
596: end if; -- no_exclusive_error
597: --
598: hr_utility.set_location(' Leaving:'||l_proc,30);
599: exception
600: when app_exception.application_exception then
601: if hr_multi_message.exception_add
602: (p_associated_column1 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
600: when app_exception.application_exception then
601: if hr_multi_message.exception_add
602: (p_associated_column1 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
603: ) then
604: hr_utility.set_location(' Leaving:'|| l_proc, 40);
605: raise;
606: end if;
607: hr_utility.set_location(' Leaving:'|| l_proc, 50);
608: End chk_offer_assignment_id;
603: ) then
604: hr_utility.set_location(' Leaving:'|| l_proc, 40);
605: raise;
606: end if;
607: hr_utility.set_location(' Leaving:'|| l_proc, 50);
608: End chk_offer_assignment_id;
609: --
610: -- ----------------------------------------------------------------------------
611: -- |---------------------------< set_vacancy_id >-----------------------------|
647: between effective_start_date
648: and effective_end_date;
649: --
650: Begin
651: hr_utility.set_location('Entering:'||l_proc,10);
652: --
653: if hr_multi_message.no_exclusive_error(
654: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
655: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
659: --
660: open csr_appl_vac_id;
661: fetch csr_appl_vac_id into l_vacancy_id;
662: --
663: hr_utility.set_location(l_proc,20);
664: if (csr_appl_vac_id%notfound)
665: then
666: --
667: hr_utility.set_location(l_proc,30);
663: hr_utility.set_location(l_proc,20);
664: if (csr_appl_vac_id%notfound)
665: then
666: --
667: hr_utility.set_location(l_proc,30);
668: --
669: close csr_appl_vac_id;
670: fnd_message.set_name('PER','IRC_412006_ASG_NOT_APPL');
671: fnd_message.raise_error;
675: p_vacancy_id := l_vacancy_id;
676: --
677: end if; -- no_exclusive_error
678: --
679: hr_utility.set_location(' Leaving:'||l_proc,40);
680: exception
681: when app_exception.application_exception then
682: if hr_multi_message.exception_add
683: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
681: when app_exception.application_exception then
682: if hr_multi_message.exception_add
683: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
684: ) then
685: hr_utility.set_location(' Leaving:'|| l_proc, 50);
686: raise;
687: end if;
688: hr_utility.set_location(' Leaving:'|| l_proc, 60);
689: End set_vacancy_id;
684: ) then
685: hr_utility.set_location(' Leaving:'|| l_proc, 50);
686: raise;
687: end if;
688: hr_utility.set_location(' Leaving:'|| l_proc, 60);
689: End set_vacancy_id;
690: --
691: -- ----------------------------------------------------------------------------
692: -- |--------------------< chk_offers_exceeds_openings >-----------------------|
790: --
791: l_vacancy_opening_count csr_vacancy_opening_count%ROWTYPE;
792: --
793: Begin
794: hr_utility.set_location('Entering:'||l_proc,10);
795: --
796: if p_offer_status = 'CLOSED'
797: then
798: --
867: open csr_vacancy_opening_count;
868: fetch csr_vacancy_opening_count into l_vacancy_opening_count;
869: close csr_vacancy_opening_count;
870: --
871: hr_utility.set_location(l_proc,20);
872: --
873: if (l_vacancy_opening_count.budget_measurement_type <> 'FTE')
874: then
875: --
887: --
888: end if; -- no_exclusive_error
889: --
890: end if; -- if l_chk_vacancy_count check
891: hr_utility.set_location(' Leaving:'||l_proc,30);
892: exception
893: when app_exception.application_exception then
894: if hr_multi_message.exception_add
895: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
893: when app_exception.application_exception then
894: if hr_multi_message.exception_add
895: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
896: ) then
897: hr_utility.set_location(' Leaving:'|| l_proc, 40);
898: raise;
899: end if;
900: hr_utility.set_location(' Leaving:'|| l_proc, 50);
901: End chk_offers_exceeds_openings;
896: ) then
897: hr_utility.set_location(' Leaving:'|| l_proc, 40);
898: raise;
899: end if;
900: hr_utility.set_location(' Leaving:'|| l_proc, 50);
901: End chk_offers_exceeds_openings;
902: --
903: -- ----------------------------------------------------------------------------
904: -- |--------------------------< chk_respondent_id >---------------------------|
939: from fnd_user
940: where user_id = p_respondent_id;
941: --
942: Begin
943: hr_utility.set_location('Entering:'||l_proc,10);
944: --
945: if hr_multi_message.no_exclusive_error(
946: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
947: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
955: l_api_updating := irc_iof_shd.api_updating
956: (p_offer_id => p_offer_id
957: ,p_object_version_number => p_object_version_number);
958: --
959: hr_utility.set_location(l_proc,20);
960:
961: if ((l_api_updating
962: and nvl(irc_iof_shd.g_old_rec.respondent_id, hr_api.g_number) <>
963: nvl(p_respondent_id, hr_api.g_number))
967: -- Check if respondent id is not null.
968: --
969: if p_respondent_id is not null then
970:
971: hr_utility.set_location(l_proc, 30);
972:
973: open csr_respondent_id;
974: fetch csr_respondent_id into l_respondent_id;
975: --
972:
973: open csr_respondent_id;
974: fetch csr_respondent_id into l_respondent_id;
975: --
976: hr_utility.set_location(l_proc,40);
977: if (csr_respondent_id%notfound)
978: then
979: close csr_respondent_id;
980: fnd_message.set_name('FND','FND_GRANTS_GNT_USER_INVALID');
981: fnd_message.raise_error;
982: end if;
983: close csr_respondent_id;
984: --
985: hr_utility.set_location(' Leaving:'||l_proc,50);
986: --
987: end if;
988: end if;
989: end if; -- no_exclusive_error
992: when app_exception.application_exception then
993: if hr_multi_message.exception_add
994: (p_associated_column1 => 'IRC_OFFERS.RESPONDENT_ID'
995: ) then
996: hr_utility.set_location(' Leaving:'|| l_proc, 60);
997: raise;
998: end if;
999: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1000: End chk_respondent_id;
995: ) then
996: hr_utility.set_location(' Leaving:'|| l_proc, 60);
997: raise;
998: end if;
999: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1000: End chk_respondent_id;
1001: --
1002: -- ----------------------------------------------------------------------------
1003: -- |---------------------------< chk_expiry_date >----------------------------|
1046: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE := irc_iof_shd.g_old_rec.offer_shipping_date;
1047: l_effective_date date;
1048: --
1049: Begin
1050: hr_utility.set_location('Entering:'||l_proc,10);
1051: --
1052: if hr_multi_message.no_exclusive_error(
1053: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1054: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1057: --
1058: if p_offer_status = 'EXTENDED'
1059: then
1060: --
1061: hr_utility.set_location(l_proc, 20);
1062: --
1063: if p_expiry_date is not null
1064: then
1065: --
1091: --
1092: if l_offer_duration_value is not null
1093: then
1094: --
1095: hr_utility.set_location(l_proc, 30);
1096: --
1097: -- Set the effective date
1098: --
1099: if l_prev_expiry_date > p_effective_date
1109: --
1110: if l_offer_duration_measurement = 'MONTH'
1111: then
1112: --
1113: hr_utility.set_location(l_proc, 60);
1114: --
1115: l_expiry_date := add_months(l_effective_date,l_offer_duration_value);
1116: --
1117: elsif l_offer_duration_measurement = 'WEEK'
1116: --
1117: elsif l_offer_duration_measurement = 'WEEK'
1118: then
1119: --
1120: hr_utility.set_location(l_proc, 50);
1121: --
1122: l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1123: --
1124: else -- By default l_offer_duration_measurement = 'DAY'
1122: l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1123: --
1124: else -- By default l_offer_duration_measurement = 'DAY'
1125: --
1126: hr_utility.set_location(l_proc, 40);
1127: --
1128: l_expiry_date := l_effective_date + l_offer_duration_value;
1129: --
1130: end if;
1149: -- Set the in out variable
1150: --
1151: p_expiry_date := l_expiry_date;
1152: --
1153: hr_utility.set_location(' Leaving:'||l_proc,70);
1154: --
1155: exception
1156: when app_exception.application_exception then
1157: if hr_multi_message.exception_add
1156: when app_exception.application_exception then
1157: if hr_multi_message.exception_add
1158: (p_associated_column1 => 'IRC_OFFERS.EXPIRY_DATE'
1159: ) then
1160: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1161: raise;
1162: end if;
1163: hr_utility.set_location(' Leaving:'|| l_proc, 90);
1164: End chk_expiry_date;
1159: ) then
1160: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1161: raise;
1162: end if;
1163: hr_utility.set_location(' Leaving:'|| l_proc, 90);
1164: End chk_expiry_date;
1165: --
1166: -- ----------------------------------------------------------------------------
1167: -- |----------------------------< set_address_id >----------------------------|
1212: between adr.date_from
1213: and nvl(adr.date_to, trunc(sysdate));
1214: --
1215: Begin
1216: hr_utility.set_location('Entering:'||l_proc,10);
1217: --
1218: if hr_multi_message.no_exclusive_error(
1219: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1220: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1220: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1221: ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1222: ) then
1223: --
1224: hr_utility.set_location(l_proc,20);
1225: --
1226: -- Default the address_id only if it is null
1227: --
1228: if p_address_id is null then
1229: --
1230: open csr_rec_address_id;
1231: fetch csr_rec_address_id into l_rec_address_id;
1232: --
1233: hr_utility.set_location(l_proc,30);
1234: if (csr_rec_address_id%found)
1235: then
1236: --
1237: -- The person has a recruiting address. Hence, default the address_id
1241: --
1242: end if;
1243: close csr_rec_address_id;
1244: --
1245: hr_utility.set_location(' Leaving:'||l_proc,40);
1246: end if;
1247: end if; -- no_exclusive_error
1248: --
1249: exception
1250: when app_exception.application_exception then
1251: if hr_multi_message.exception_add
1252: (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1253: ) then
1254: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1255: raise;
1256: end if;
1257: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1258: End set_address_id;
1253: ) then
1254: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1255: raise;
1256: end if;
1257: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1258: End set_address_id;
1259: --
1260: -- ----------------------------------------------------------------------------
1261: -- |----------------------------< chk_address_id >----------------------------|
1296: from per_addresses
1297: where address_id = p_address_id;
1298: --
1299: Begin
1300: hr_utility.set_location('Entering:'||l_proc,10);
1301: --
1302: if hr_multi_message.no_exclusive_error(
1303: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1304: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1312: l_api_updating := irc_iof_shd.api_updating
1313: (p_offer_id => p_offer_id
1314: ,p_object_version_number => p_object_version_number);
1315: --
1316: hr_utility.set_location(l_proc,20);
1317:
1318: if ((l_api_updating
1319: and nvl(irc_iof_shd.g_old_rec.address_id, hr_api.g_number) <>
1320: nvl(p_address_id, hr_api.g_number))
1327: --
1328: open csr_address_id;
1329: fetch csr_address_id into l_address_id;
1330: --
1331: hr_utility.set_location(l_proc,30);
1332: if (csr_address_id%notfound)
1333: then
1334: close csr_address_id;
1335: fnd_message.set_name('PER','IRC_412001_BAD_ADDRESS_ID');
1336: fnd_message.raise_error;
1337: end if;
1338: close csr_address_id;
1339: --
1340: hr_utility.set_location(' Leaving:'||l_proc,40);
1341: end if;
1342: end if;
1343: end if; -- no_exclusive_error
1344: --
1346: when app_exception.application_exception then
1347: if hr_multi_message.exception_add
1348: (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1349: ) then
1350: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1351: raise;
1352: end if;
1353: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1354: End chk_address_id;
1349: ) then
1350: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1351: raise;
1352: end if;
1353: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1354: End chk_address_id;
1355: --
1356: -- ----------------------------------------------------------------------------
1357: -- |----------------------------< chk_template_id >---------------------------|
1430: and iof.offer_assignment_id = ppaf.assignment_id
1431: and ita.organization_id = ppaf.organization_id;
1432: --
1433: Begin
1434: hr_utility.set_location('Entering:'||l_proc,10);
1435: --
1436: if hr_multi_message.no_exclusive_error(
1437: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1438: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1446: l_api_updating := irc_iof_shd.api_updating
1447: (p_offer_id => p_offer_id
1448: ,p_object_version_number => p_object_version_number);
1449: --
1450: hr_utility.set_location(l_proc,20);
1451:
1452: if ((l_api_updating
1453: and nvl(irc_iof_shd.g_old_rec.template_id, hr_api.g_number) <>
1454: nvl(p_template_id, hr_api.g_number)) or
1457: -- Check if template id is not null and if it is active
1458: --
1459: if p_template_id is not null then
1460: --
1461: hr_utility.set_location(l_proc,30);
1462: --
1463: open csr_template_id;
1464: fetch csr_template_id into l_template_id;
1465: --
1465: --
1466: if (csr_template_id%notfound)
1467: then
1468: --
1469: hr_utility.set_location(l_proc,40);
1470: --
1471: close csr_template_id;
1472: fnd_message.set_name('PER','IRC_412326_OFFER_INV_TEMPLT_ID');
1473: fnd_message.raise_error;
1475: close csr_template_id;
1476: --
1477: else
1478: --
1479: hr_utility.set_location(l_proc,50);
1480: --
1481: open csr_default_template_job;
1482: fetch csr_default_template_job into l_template_id;
1483: --
1483: --
1484: if (csr_default_template_job%notfound)
1485: then
1486: --
1487: hr_utility.set_location(l_proc,60);
1488: --
1489: close csr_default_template_job;
1490: --
1491: open csr_default_template_pos;
1493: --
1494: if (csr_default_template_pos%notfound)
1495: then
1496: --
1497: hr_utility.set_location(l_proc,70);
1498: --
1499: close csr_default_template_pos;
1500: --
1501: open csr_default_template_org;
1503: --
1504: if (csr_default_template_org%notfound)
1505: then
1506: --
1507: hr_utility.set_location(l_proc,80);
1508: --
1509: close csr_default_template_org;
1510: l_template_id := p_template_id;
1511: --
1515: --
1516: end if; -- if - else - endif;
1517: end if; -- l_api_updating
1518: end if; -- no_exclusive_error
1519: hr_utility.set_location(' Leaving:'||l_proc,90);
1520: --
1521: exception
1522: when app_exception.application_exception then
1523: if hr_multi_message.exception_add
1522: when app_exception.application_exception then
1523: if hr_multi_message.exception_add
1524: (p_associated_column1 => 'IRC_OFFERS.TEMPLATE_ID'
1525: ) then
1526: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1527: raise;
1528: end if;
1529: hr_utility.set_location(' Leaving:'|| l_proc, 110);
1530: End chk_template_id;
1525: ) then
1526: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1527: raise;
1528: end if;
1529: hr_utility.set_location(' Leaving:'|| l_proc, 110);
1530: End chk_template_id;
1531: --
1532: -- ----------------------------------------------------------------------------
1533: -- |--------------------------< gen_offer_version >---------------------------|
1572: );
1573:
1574: --
1575: Begin
1576: hr_utility.set_location('Entering:'||l_proc,10);
1577: --
1578: hr_api.mandatory_arg_error
1579: (p_api_name => l_proc
1580: ,p_argument => 'APPLICANT_ASSIGNMENT_ID'
1588: fetch csr_get_offer_version into l_offer_version;
1589: close csr_get_offer_version;
1590: p_offer_version := l_offer_version;
1591: --
1592: hr_utility.set_location(' Leaving:'||l_proc,20);
1593: --
1594: exception
1595: when app_exception.application_exception then
1596: if hr_multi_message.exception_add
1595: when app_exception.application_exception then
1596: if hr_multi_message.exception_add
1597: (p_associated_column1 => 'IRC_OFFERS.OFFER_VERSION'
1598: ) then
1599: hr_utility.set_location(' Leaving:'|| l_proc, 30);
1600: raise;
1601: end if;
1602: hr_utility.set_location(' Leaving:'|| l_proc, 40);
1603: End gen_offer_version;
1598: ) then
1599: hr_utility.set_location(' Leaving:'|| l_proc, 30);
1600: raise;
1601: end if;
1602: hr_utility.set_location(' Leaving:'|| l_proc, 40);
1603: End gen_offer_version;
1604: --
1605: -- ----------------------------------------------------------------------------
1606: -- |--------------------------< chk_latest_offer >---------------------------|
1677: where latest_offer = 'Y'
1678: and applicant_assignment_id = p_applicant_assignment_id;
1679: --
1680: Begin
1681: hr_utility.set_location('Entering:'||l_proc,10);
1682: --
1683: if hr_multi_message.no_exclusive_error(
1684: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1685: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1693: l_api_updating := irc_iof_shd.api_updating
1694: (p_offer_id => p_offer_id
1695: ,p_object_version_number => p_object_version_number);
1696: --
1697: hr_utility.set_location(l_proc,20);
1698: --
1699: -- If a newly created offer is in SFL status, there
1700: -- should not be a validation as the offer_status for this
1701: -- offer will always be 'N'.
1707: --
1708: open csr_latest_offer_ins;
1709: fetch csr_latest_offer_ins into l_latest_offer;
1710: --
1711: hr_utility.set_location(l_proc,30);
1712: if (csr_latest_offer_ins%found)
1713: then
1714: close csr_latest_offer_ins;
1715: fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1728: --
1729: open csr_latest_offer_upd;
1730: fetch csr_latest_offer_upd into l_latest_offer;
1731: --
1732: hr_utility.set_location(l_proc,30);
1733: if (csr_latest_offer_upd%found)
1734: then
1735: close csr_latest_offer_upd;
1736: fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1746: and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1747: nvl(p_latest_offer, hr_api.g_varchar2))
1748: or (NOT l_api_updating)) then
1749: --
1750: hr_utility.set_location(l_proc,40);
1751: --
1752: -- Checks that the value for latest_offer is
1753: -- valid and exists on YES_NO lookup
1754: --
1759: ) then
1760: --
1761: -- Error: Invalid latest offer value.
1762: --
1763: hr_utility.set_location(l_proc,50);
1764: --
1765: fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1766: fnd_message.raise_error;
1767: end if;
1765: fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1766: fnd_message.raise_error;
1767: end if;
1768: --
1769: hr_utility.set_location(' Leaving:'||l_proc,60);
1770: end if;
1771: end if; -- no_exclusive_error
1772: --
1773: exception
1774: when app_exception.application_exception then
1775: if hr_multi_message.exception_add
1776: (p_associated_column1 => 'IRC_OFFERS.LATEST_OFFER'
1777: ) then
1778: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1779: raise;
1780: end if;
1781: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1782: End chk_latest_offer;
1777: ) then
1778: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1779: raise;
1780: end if;
1781: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1782: End chk_latest_offer;
1783: --
1784: -- ----------------------------------------------------------------------------
1785: -- |--------------------< chk_offer_version_combination >---------------------|
1826: and applicant_assignment_id = p_applicant_assignment_id;
1827: --
1828: Begin
1829: --
1830: hr_utility.set_location(' Entering:'||l_proc,10);
1831: --
1832: if hr_multi_message.no_exclusive_error(
1833: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1834: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1854: (NOT l_api_updating)) then
1855: --
1856: open csr_version;
1857: fetch csr_version into l_version;
1858: hr_utility.set_location(l_proc,20);
1859: if csr_version%found then
1860: close csr_version;
1861: fnd_message.set_name(800,'IRC_412308_INV_OFFER_VER_COMB');
1862: fnd_message.raise_error;
1870: if hr_multi_message.exception_add
1871: (p_associated_column1 => 'IRC_OFFER.OFFER_VERSION'
1872: ,p_associated_column2 => 'IRC_OFFER.APPLICANT_ASSIGNMENT_ID'
1873: ) then
1874: hr_utility.set_location(' Leaving:'||l_proc,30);
1875: raise;
1876: end if;
1877: --
1878: hr_utility.set_location(' Leaving:'||l_proc,40);
1874: hr_utility.set_location(' Leaving:'||l_proc,30);
1875: raise;
1876: end if;
1877: --
1878: hr_utility.set_location(' Leaving:'||l_proc,40);
1879: End chk_offer_version_combination;
1880: --
1881: -- ---------------------------------------------------------------------------
1882: -- |--------------------------< chk_offer_status >---------------------------|
1915: l_proc varchar2(72) := g_package||'chk_offer_status';
1916: l_api_updating boolean;
1917: --
1918: begin
1919: hr_utility.set_location('Entering:'|| l_proc, 10);
1920: --
1921: if hr_multi_message.no_exclusive_error(
1922: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1923: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1946: nvl(p_offer_status, hr_api.g_varchar2))
1947: or
1948: (NOT l_api_updating)) then
1949: --
1950: hr_utility.set_location(l_proc, 20);
1951: --
1952: -- Checks that the value for offer_status is
1953: -- valid and exists on irc_offer_statuses within
1954: -- the specified date range
1958: ,p_lookup_type => 'IRC_OFFER_STATUSES'
1959: ,p_lookup_code => p_offer_status
1960: ) then
1961: --
1962: hr_utility.set_location(l_proc, 30);
1963: -- Error: Invalid offer status type.
1964: fnd_message.set_name('PER', 'IRC_412323_INV_OFFER_STATUS');
1965: fnd_message.raise_error;
1966: end if;
1971: --
1972: if NOT l_api_updating
1973: then
1974: --
1975: hr_utility.set_location(l_proc, 40);
1976: if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1977: then
1978: --
1979: hr_utility.set_location(l_proc, 50);
1975: hr_utility.set_location(l_proc, 40);
1976: if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1977: then
1978: --
1979: hr_utility.set_location(l_proc, 50);
1980: fnd_message.set_name('PER', 'IRC_412309_INV_CRT_OFR_STATUS');
1981: fnd_message.raise_error;
1982: --
1983: end if;
1985: --
1986: --
1987: end if; -- no_exclusive_error
1988: --
1989: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1990: exception
1991: when app_exception.application_exception then
1992: if hr_multi_message.exception_add
1993: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1991: when app_exception.application_exception then
1992: if hr_multi_message.exception_add
1993: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1994: ) then
1995: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1996: raise;
1997: end if;
1998: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1999: end chk_offer_status;
1994: ) then
1995: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1996: raise;
1997: end if;
1998: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1999: end chk_offer_status;
2000: --
2001: --
2002: -- ---------------------------------------------------------------------------
2060: AND ios3.status_change_date > ios1.status_change_date
2061: );
2062: --
2063: begin
2064: hr_utility.set_location('Entering:'|| l_proc, 10);
2065: --
2066: if p_current_offer_record.offer_status <> 'CLOSED'
2067: and p_current_offer_record.offer_status <> 'APPROVED'
2068: and p_current_offer_record.offer_status <> 'CORRECTION'
2068: and p_current_offer_record.offer_status <> 'CORRECTION'
2069: and p_current_offer_record.offer_status <> 'EXTENDED'
2070: then
2071: --
2072: hr_utility.set_location(l_proc,20);
2073: --
2074: -- Check to see if the offer status is 'Hold'.
2075: --
2076: if ( p_current_offer_record.offer_status = 'HOLD'
2075: --
2076: if ( p_current_offer_record.offer_status = 'HOLD'
2077: ) then
2078: --
2079: hr_utility.set_location(l_proc,30);
2080: --
2081: -- Check if the offer was previously in 'Hold'
2082: -- state too. If so, the offer has been updated. Hence,
2083: -- throw an error saying that the offer cannot be updated.
2084: --
2085: if ( l_prev_offer_status = 'HOLD'
2086: ) then
2087: --
2088: hr_utility.set_location(l_proc,40);
2089: --
2090: fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2091: fnd_message.raise_error;
2092: end if;
2145: );
2146: if ( l_mutiple_fields_updated = true )
2147: then
2148: --
2149: hr_utility.set_location(l_proc,45);
2150: --
2151: fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2152: fnd_message.raise_error;
2153: end if;
2155: else
2156: --
2157: -- If the offer status is anything else.
2158: --
2159: hr_utility.set_location(l_proc,50);
2160: --
2161: -- Check if the offer was previously in 'Hold' State.
2162: -- If so, the current state should be the state which existed
2163: -- before the offer was Held.
2164: --
2165: if ( l_prev_offer_status = 'HOLD'
2166: ) then
2167: --
2168: hr_utility.set_location(l_proc,60);
2169: --
2170: open csr_prev_to_prev_offer_status;
2171: fetch csr_prev_to_prev_offer_status into l_prev_to_prev_offer_status;
2172: close csr_prev_to_prev_offer_status;
2173: --
2174: if ( p_current_offer_record.offer_status <> l_prev_to_prev_offer_status
2175: ) then
2176: --
2177: hr_utility.set_location(l_proc,70);
2178: --
2179: fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
2180: fnd_message.raise_error;
2181: --
2183: end if;
2184: end if; -- if-else-end if
2185: end if; -- Offer_status = 'CLOSED'
2186: --
2187: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2188: exception
2189: when app_exception.application_exception then
2190: if hr_multi_message.exception_add
2191: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2189: when app_exception.application_exception then
2190: if hr_multi_message.exception_add
2191: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2192: ) then
2193: hr_utility.set_location(' Leaving:'|| l_proc, 90);
2194: raise;
2195: end if;
2196: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2197: end chk_offer_status_update;
2192: ) then
2193: hr_utility.set_location(' Leaving:'|| l_proc, 90);
2194: raise;
2195: end if;
2196: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2197: end chk_offer_status_update;
2198: --
2199: -- ----------------------------------------------------------------------------
2200: -- |---------------------< chk_multiple_fields_updated >----------------------|
2332: --
2333: l_offer_old_rec C_Sel1%ROWTYPE;
2334: Begin
2335: --
2336: hr_utility.set_location('Entering:'||l_proc,10);
2337: --
2338: Open C_Sel1;
2339: Fetch C_Sel1 Into l_offer_old_rec;
2340: If C_Sel1%notfound Then
2354: nvl(l_offer_old_rec.offer_id, hr_api.g_number) <>
2355: nvl(p_offer_id, hr_api.g_number)
2356: then
2357: --
2358: hr_utility.set_location(l_proc,20);
2359: --
2360: l_update_count := l_update_count + 1;
2361: end if;
2362: end if;
2362: end if;
2363:
2364: if(p_offer_status <> hr_api.g_varchar2) then
2365: --
2366: hr_utility.set_location(l_proc,30);
2367: --
2368: if
2369: nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2370: nvl(p_offer_status, hr_api.g_varchar2)
2369: nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2370: nvl(p_offer_status, hr_api.g_varchar2)
2371: then
2372: --
2373: hr_utility.set_location(l_proc,50);
2374: --
2375: l_update_count := l_update_count + 1;
2376: end if;
2377: end if;
2383: nvl(l_offer_old_rec.discretionary_job_title, hr_api.g_varchar2) <>
2384: nvl(p_discretionary_job_title, hr_api.g_varchar2)
2385: then
2386: --
2387: hr_utility.set_location(l_proc,60);
2388: --
2389: l_update_count := l_update_count + 1;
2390: end if;
2391: end if;
2397: nvl(l_offer_old_rec.offer_extended_method, hr_api.g_varchar2) <>
2398: nvl(p_offer_extended_method, hr_api.g_varchar2)
2399: then
2400: --
2401: hr_utility.set_location(l_proc,70);
2402: --
2403: l_update_count := l_update_count + 1;
2404: end if;
2405: end if;
2411: nvl(l_offer_old_rec.expiry_date, hr_api.g_date) <>
2412: nvl(p_expiry_date, hr_api.g_date)
2413: then
2414: --
2415: hr_utility.set_location(l_proc,90);
2416: --
2417: l_update_count := l_update_count + 1;
2418: end if;
2419: end if;
2425: nvl(l_offer_old_rec.proposed_start_date, hr_api.g_date) <>
2426: nvl(p_proposed_start_date, hr_api.g_date)
2427: then
2428: --
2429: hr_utility.set_location(l_proc,100);
2430: --
2431: l_update_count := l_update_count + 1;
2432: end if;
2433: end if;
2439: nvl(l_offer_old_rec.offer_letter_tracking_code, hr_api.g_varchar2) <>
2440: nvl(p_offer_letter_tracking_code, hr_api.g_varchar2)
2441: then
2442: --
2443: hr_utility.set_location(l_proc,110);
2444: --
2445: l_update_count := l_update_count + 1;
2446: end if;
2447: end if;
2453: nvl(l_offer_old_rec.offer_postal_service, hr_api.g_varchar2) <>
2454: nvl(p_offer_postal_service, hr_api.g_varchar2)
2455: then
2456: --
2457: hr_utility.set_location(l_proc,120);
2458: --
2459: l_update_count := l_update_count + 1;
2460: end if;
2461: end if;
2467: nvl(l_offer_old_rec.offer_shipping_date, hr_api.g_date) <>
2468: nvl(p_offer_shipping_date, hr_api.g_date)
2469: then
2470: --
2471: hr_utility.set_location(l_proc,130);
2472: --
2473: l_update_count := l_update_count + 1;
2474: end if;
2475: end if;
2480: nvl(l_offer_old_rec.applicant_assignment_id, hr_api.g_number) <>
2481: nvl(p_applicant_assignment_id, hr_api.g_number)
2482: then
2483: --
2484: hr_utility.set_location(l_proc,150);
2485: --
2486: l_update_count := l_update_count + 1;
2487: end if;
2488: end if;
2492: nvl(l_offer_old_rec.offer_assignment_id, hr_api.g_number) <>
2493: nvl(p_offer_assignment_id, hr_api.g_number)
2494: then
2495: --
2496: hr_utility.set_location(l_proc,160);
2497: --
2498: l_update_count := l_update_count + 1;
2499: end if;
2500: end if;
2506: nvl(l_offer_old_rec.address_id, hr_api.g_number) <>
2507: nvl(p_address_id, hr_api.g_number)
2508: then
2509: --
2510: hr_utility.set_location(l_proc,170);
2511: --
2512: l_update_count := l_update_count + 1;
2513: end if;
2514: end if;
2520: nvl(l_offer_old_rec.template_id, hr_api.g_number) <>
2521: nvl(p_template_id, hr_api.g_number)
2522: then
2523: --
2524: hr_utility.set_location(l_proc,180);
2525: --
2526: l_update_count := l_update_count + 1;
2527: end if;
2528: end if;
2534: nvl(l_offer_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
2535: nvl(p_offer_letter_file_type, hr_api.g_varchar2)
2536: then
2537: --
2538: hr_utility.set_location(l_proc,190);
2539: --
2540: l_update_count := l_update_count + 1;
2541: end if;
2542: end if;
2548: nvl(l_offer_old_rec.offer_letter_file_name, hr_api.g_varchar2) <>
2549: nvl(p_offer_letter_file_name, hr_api.g_varchar2)
2550: then
2551: --
2552: hr_utility.set_location(l_proc,200);
2553: --
2554: l_update_count := l_update_count + 1;
2555: end if;
2556: end if;
2562: nvl(l_offer_old_rec.attribute_category, hr_api.g_varchar2) <>
2563: nvl(p_attribute_category, hr_api.g_varchar2)
2564: then
2565: --
2566: hr_utility.set_location(l_proc,210);
2567: --
2568: l_update_count := l_update_count + 1;
2569: end if;
2570: end if;
2576: nvl(l_offer_old_rec.attribute1, hr_api.g_varchar2) <>
2577: nvl(p_attribute1, hr_api.g_varchar2)
2578: then
2579: --
2580: hr_utility.set_location(l_proc,220);
2581: --
2582: l_update_count := l_update_count + 1;
2583: end if;
2584: end if;
2590: nvl(l_offer_old_rec.attribute2, hr_api.g_varchar2) <>
2591: nvl(p_attribute2, hr_api.g_varchar2)
2592: then
2593: --
2594: hr_utility.set_location(l_proc,230);
2595: --
2596: l_update_count := l_update_count + 1;
2597: end if;
2598: end if;
2604: nvl(l_offer_old_rec.attribute3, hr_api.g_varchar2) <>
2605: nvl(p_attribute3, hr_api.g_varchar2)
2606: then
2607: --
2608: hr_utility.set_location(l_proc,240);
2609: --
2610: l_update_count := l_update_count + 1;
2611: end if;
2612: end if;
2618: nvl(l_offer_old_rec.attribute4, hr_api.g_varchar2) <>
2619: nvl(p_attribute4, hr_api.g_varchar2)
2620: then
2621: --
2622: hr_utility.set_location(l_proc,250);
2623: --
2624: l_update_count := l_update_count + 1;
2625: end if;
2626: end if;
2632: nvl(l_offer_old_rec.attribute5, hr_api.g_varchar2) <>
2633: nvl(p_attribute5, hr_api.g_varchar2)
2634: then
2635: --
2636: hr_utility.set_location(l_proc,260);
2637: --
2638: l_update_count := l_update_count + 1;
2639: end if;
2640: end if;
2646: nvl(l_offer_old_rec.attribute6, hr_api.g_varchar2) <>
2647: nvl(p_attribute6, hr_api.g_varchar2)
2648: then
2649: --
2650: hr_utility.set_location(l_proc,270);
2651: --
2652: l_update_count := l_update_count + 1;
2653: end if;
2654: end if;
2660: nvl(l_offer_old_rec.attribute7, hr_api.g_varchar2) <>
2661: nvl(p_attribute7, hr_api.g_varchar2)
2662: then
2663: --
2664: hr_utility.set_location(l_proc,280);
2665: --
2666: l_update_count := l_update_count + 1;
2667: end if;
2668: end if;
2674: nvl(l_offer_old_rec.attribute8, hr_api.g_varchar2) <>
2675: nvl(p_attribute8, hr_api.g_varchar2)
2676: then
2677: --
2678: hr_utility.set_location(l_proc,290);
2679: --
2680: l_update_count := l_update_count + 1;
2681: end if;
2682: end if;
2688: nvl(l_offer_old_rec.attribute9, hr_api.g_varchar2) <>
2689: nvl(p_attribute9, hr_api.g_varchar2)
2690: then
2691: --
2692: hr_utility.set_location(l_proc,300);
2693: --
2694: l_update_count := l_update_count + 1;
2695: end if;
2696: end if;
2702: nvl(l_offer_old_rec.attribute10, hr_api.g_varchar2) <>
2703: nvl(p_attribute10, hr_api.g_varchar2)
2704: then
2705: --
2706: hr_utility.set_location(l_proc,310);
2707: --
2708: l_update_count := l_update_count + 1;
2709: end if;
2710: end if;
2716: nvl(l_offer_old_rec.attribute11, hr_api.g_varchar2) <>
2717: nvl(p_attribute11, hr_api.g_varchar2)
2718: then
2719: --
2720: hr_utility.set_location(l_proc,320);
2721: --
2722: l_update_count := l_update_count + 1;
2723: end if;
2724: end if;
2730: nvl(l_offer_old_rec.attribute12, hr_api.g_varchar2) <>
2731: nvl(p_attribute12, hr_api.g_varchar2)
2732: then
2733: --
2734: hr_utility.set_location(l_proc,330);
2735: --
2736: l_update_count := l_update_count + 1;
2737: end if;
2738: end if;
2744: nvl(l_offer_old_rec.attribute13, hr_api.g_varchar2) <>
2745: nvl(p_attribute13, hr_api.g_varchar2)
2746: then
2747: --
2748: hr_utility.set_location(l_proc,340);
2749: --
2750: l_update_count := l_update_count + 1;
2751: end if;
2752: end if;
2758: nvl(l_offer_old_rec.attribute14, hr_api.g_varchar2) <>
2759: nvl(p_attribute14, hr_api.g_varchar2)
2760: then
2761: --
2762: hr_utility.set_location(l_proc,350);
2763: --
2764: l_update_count := l_update_count + 1;
2765: end if;
2766: end if;
2772: nvl(l_offer_old_rec.attribute15, hr_api.g_varchar2) <>
2773: nvl(p_attribute15, hr_api.g_varchar2)
2774: then
2775: --
2776: hr_utility.set_location(l_proc,360);
2777: --
2778: l_update_count := l_update_count + 1;
2779: end if;
2780: end if;
2786: nvl(l_offer_old_rec.attribute16, hr_api.g_varchar2) <>
2787: nvl(p_attribute16, hr_api.g_varchar2)
2788: then
2789: --
2790: hr_utility.set_location(l_proc,370);
2791: --
2792: l_update_count := l_update_count + 1;
2793: end if;
2794: end if;
2800: nvl(l_offer_old_rec.attribute17, hr_api.g_varchar2) <>
2801: nvl(p_attribute17, hr_api.g_varchar2)
2802: then
2803: --
2804: hr_utility.set_location(l_proc,380);
2805: --
2806: l_update_count := l_update_count + 1;
2807: end if;
2808: end if;
2814: nvl(l_offer_old_rec.attribute18, hr_api.g_varchar2) <>
2815: nvl(p_attribute18, hr_api.g_varchar2)
2816: then
2817: --
2818: hr_utility.set_location(l_proc,390);
2819: --
2820: l_update_count := l_update_count + 1;
2821: end if;
2822: end if;
2828: nvl(l_offer_old_rec.attribute19, hr_api.g_varchar2) <>
2829: nvl(p_attribute19, hr_api.g_varchar2)
2830: then
2831: --
2832: hr_utility.set_location(l_proc,400);
2833: --
2834: l_update_count := l_update_count + 1;
2835: end if;
2836: end if;
2842: nvl(l_offer_old_rec.attribute20, hr_api.g_varchar2) <>
2843: nvl(p_attribute20, hr_api.g_varchar2)
2844: then
2845: --
2846: hr_utility.set_location(l_proc,410);
2847: --
2848: l_update_count := l_update_count + 1;
2849: end if;
2850: end if;
2856: nvl(l_offer_old_rec.attribute21, hr_api.g_varchar2) <>
2857: nvl(p_attribute21, hr_api.g_varchar2)
2858: then
2859: --
2860: hr_utility.set_location(l_proc,420);
2861: --
2862: l_update_count := l_update_count + 1;
2863: end if;
2864: end if;
2870: nvl(l_offer_old_rec.attribute22, hr_api.g_varchar2) <>
2871: nvl(p_attribute22, hr_api.g_varchar2)
2872: then
2873: --
2874: hr_utility.set_location(l_proc,430);
2875: --
2876: l_update_count := l_update_count + 1;
2877: end if;
2878: end if;
2884: nvl(l_offer_old_rec.attribute23, hr_api.g_varchar2) <>
2885: nvl(p_attribute23, hr_api.g_varchar2)
2886: then
2887: --
2888: hr_utility.set_location(l_proc,440);
2889: --
2890: l_update_count := l_update_count + 1;
2891: end if;
2892: end if;
2898: nvl(l_offer_old_rec.attribute24, hr_api.g_varchar2) <>
2899: nvl(p_attribute24, hr_api.g_varchar2)
2900: then
2901: --
2902: hr_utility.set_location(l_proc,450);
2903: --
2904: l_update_count := l_update_count + 1;
2905: end if;
2906: end if;
2912: nvl(l_offer_old_rec.attribute25, hr_api.g_varchar2) <>
2913: nvl(p_attribute25, hr_api.g_varchar2)
2914: then
2915: --
2916: hr_utility.set_location(l_proc,460);
2917: --
2918: l_update_count := l_update_count + 1;
2919: end if;
2920: end if;
2926: nvl(l_offer_old_rec.attribute26, hr_api.g_varchar2) <>
2927: nvl(p_attribute26, hr_api.g_varchar2)
2928: then
2929: --
2930: hr_utility.set_location(l_proc,470);
2931: --
2932: l_update_count := l_update_count + 1;
2933: end if;
2934: end if;
2940: nvl(l_offer_old_rec.attribute27, hr_api.g_varchar2) <>
2941: nvl(p_attribute27, hr_api.g_varchar2)
2942: then
2943: --
2944: hr_utility.set_location(l_proc,480);
2945: --
2946: l_update_count := l_update_count + 1;
2947: end if;
2948: end if;
2954: nvl(l_offer_old_rec.attribute28, hr_api.g_varchar2) <>
2955: nvl(p_attribute28, hr_api.g_varchar2)
2956: then
2957: --
2958: hr_utility.set_location(l_proc,490);
2959: --
2960: l_update_count := l_update_count + 1;
2961: end if;
2962: end if;
2968: nvl(l_offer_old_rec.attribute29, hr_api.g_varchar2) <>
2969: nvl(p_attribute29, hr_api.g_varchar2)
2970: then
2971: --
2972: hr_utility.set_location(l_proc,500);
2973: --
2974: l_update_count := l_update_count + 1;
2975: end if;
2976: end if;
2982: nvl(l_offer_old_rec.attribute30, hr_api.g_varchar2) <>
2983: nvl(p_attribute30, hr_api.g_varchar2)
2984: then
2985: --
2986: hr_utility.set_location(l_proc,510);
2987: --
2988: l_update_count := l_update_count + 1;
2989: end if;
2990: end if;
2994: --
2995: if l_update_count > 1
2996: then
2997: --
2998: hr_utility.set_location(l_proc,520);
2999: --
3000: p_mutiple_fields_updated := true;
3001: else
3002: --
2999: --
3000: p_mutiple_fields_updated := true;
3001: else
3002: --
3003: hr_utility.set_location(l_proc,530);
3004: --
3005: p_mutiple_fields_updated := false;
3006: end if;
3007: hr_utility.set_location(' Leaving:'||l_proc,540);
3003: hr_utility.set_location(l_proc,530);
3004: --
3005: p_mutiple_fields_updated := false;
3006: end if;
3007: hr_utility.set_location(' Leaving:'||l_proc,540);
3008: --
3009: exception
3010: when others then
3011: hr_utility.set_location(' Leaving:'||l_proc,550);
3007: hr_utility.set_location(' Leaving:'||l_proc,540);
3008: --
3009: exception
3010: when others then
3011: hr_utility.set_location(' Leaving:'||l_proc,550);
3012: raise;
3013: End chk_multiple_fields_updated;
3014: --
3015: --
3050: l_proc varchar2(72) := g_package||'chk_offer_extended_method';
3051: l_api_updating boolean;
3052: --
3053: begin
3054: hr_utility.set_location('Entering:'|| l_proc, 10);
3055: --
3056: if hr_multi_message.no_exclusive_error(
3057: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3058: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3084: -- Check if offer_extended_method is not null.
3085: --
3086: if p_offer_extended_method is not null then
3087: --
3088: hr_utility.set_location(l_proc, 20);
3089: --
3090: -- Checks that the value for offer_extended_method is
3091: -- valid and exists on irc_offer_extended_method lookup
3092: -- within the specified date range
3104: end if;
3105: end if;
3106: end if; -- no_exclusive_error
3107: --
3108: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3109: exception
3110: when app_exception.application_exception then
3111: if hr_multi_message.exception_add
3112: (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3110: when app_exception.application_exception then
3111: if hr_multi_message.exception_add
3112: (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3113: ) then
3114: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3115: raise;
3116: end if;
3117: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3118: end chk_offer_extended_method;
3113: ) then
3114: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3115: raise;
3116: end if;
3117: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3118: end chk_offer_extended_method;
3119: --
3120: -- ---------------------------------------------------------------------------
3121: -- |---------------------< chk_offer_postal_service >------------------------|
3154: l_proc varchar2(72) := g_package||'chk_offer_postal_service';
3155: l_api_updating boolean;
3156: --
3157: begin
3158: hr_utility.set_location('Entering:'|| l_proc, 10);
3159: --
3160: if hr_multi_message.no_exclusive_error(
3161: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3162: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3188: -- Check if offer_postal_service is not null.
3189: --
3190: if p_offer_postal_service is not null then
3191: --
3192: hr_utility.set_location(l_proc, 20);
3193: --
3194: -- Checks that the value for offer_postal_service is
3195: -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3196: -- within the specified date range
3208: end if;
3209: end if;
3210: end if; -- no_exclusive_error
3211: --
3212: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3213: exception
3214: when app_exception.application_exception then
3215: if hr_multi_message.exception_add
3216: (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3214: when app_exception.application_exception then
3215: if hr_multi_message.exception_add
3216: (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3217: ) then
3218: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3219: raise;
3220: end if;
3221: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3222: end chk_offer_postal_service;
3217: ) then
3218: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3219: raise;
3220: end if;
3221: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3222: end chk_offer_postal_service;
3223: --
3224: -- ---------------------------------------------------------------------------
3225: -- |--------------------------< chk_offer_letter >---------------------------|
3259: from irc_offers
3260: where offer_id = p_offer_id;
3261: --
3262: begin
3263: hr_utility.set_location('Entering:'|| l_proc, 10);
3264: --
3265: if hr_multi_message.no_exclusive_error(
3266: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3267: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3272: and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
3273: nvl(p_offer_status, hr_api.g_varchar2)
3274: then
3275: --
3276: hr_utility.set_location(l_proc, 20);
3277: --
3278: open csr_offer_letter;
3279: fetch csr_offer_letter into l_offer_letter;
3280: if csr_offer_letter%notfound
3279: fetch csr_offer_letter into l_offer_letter;
3280: if csr_offer_letter%notfound
3281: then
3282: --
3283: hr_utility.set_location(l_proc, 30);
3284: --
3285: fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
3286: fnd_message.raise_error;
3287: --
3292: --
3293: if dbms_lob.getlength(l_offer_letter) = 0
3294: then
3295: --
3296: hr_utility.set_location(l_proc, 40);
3297: --
3298: fnd_message.set_name('PER','IRC_412312_UPLOAD_OFFER_LETTER');
3299: hr_multi_message.add
3300: (p_message_type => hr_multi_message.g_warning_msg
3304: --
3305: end if;
3306: end if; -- no_exclusive_error
3307: --
3308: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3309: exception
3310: when app_exception.application_exception then
3311: if hr_multi_message.exception_add
3312: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3310: when app_exception.application_exception then
3311: if hr_multi_message.exception_add
3312: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3313: ) then
3314: hr_utility.set_location(' Leaving:'|| l_proc, 60);
3315: raise;
3316: end if;
3317: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3318: end chk_offer_letter;
3313: ) then
3314: hr_utility.set_location(' Leaving:'|| l_proc, 60);
3315: raise;
3316: end if;
3317: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3318: end chk_offer_letter;
3319: --
3320: --
3321: -- ---------------------------------------------------------------------------
3355: l_proc varchar2(72) := g_package||'chk_offer_letter_file_type';
3356: l_api_updating boolean;
3357: --
3358: begin
3359: hr_utility.set_location('Entering:'|| l_proc, 10);
3360: --
3361: if hr_multi_message.no_exclusive_error(
3362: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3363: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3389: -- Check if offer_postal_service is not null.
3390: --
3391: if p_offer_letter_file_type is not null then
3392: --
3393: hr_utility.set_location(l_proc, 20);
3394: --
3395: -- Checks that the value for offer_postal_service is
3396: -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3397: -- within the specified date range
3408: end if;
3409: end if;
3410: end if; -- no_exclusive_error
3411: --
3412: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3413: exception
3414: when app_exception.application_exception then
3415: if hr_multi_message.exception_add
3416: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3414: when app_exception.application_exception then
3415: if hr_multi_message.exception_add
3416: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3417: ) then
3418: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3419: raise;
3420: end if;
3421: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3422: end chk_offer_letter_file_type;
3417: ) then
3418: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3419: raise;
3420: end if;
3421: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3422: end chk_offer_letter_file_type;
3423: --
3424: -- ----------------------------------------------------------------------------
3425: -- |---------------------------< insert_validate >----------------------------|
3432: --
3433: l_proc varchar2(72) := g_package||'insert_validate';
3434: --
3435: Begin
3436: hr_utility.set_location('Entering:'||l_proc, 10);
3437: --
3438: -- Call all supporting business operations
3439: --
3440: hr_utility.set_location(l_proc, 20);
3436: hr_utility.set_location('Entering:'||l_proc, 10);
3437: --
3438: -- Call all supporting business operations
3439: --
3440: hr_utility.set_location(l_proc, 20);
3441: --
3442: chk_applicant_assignment_id
3443: (p_effective_date => p_effective_date
3444: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3443: (p_effective_date => p_effective_date
3444: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3445: );
3446: --
3447: hr_utility.set_location(l_proc, 30);
3448: --
3449: chk_offer_assignment_id
3450: (p_offer_assignment_id => p_rec.offer_assignment_id
3451: );
3449: chk_offer_assignment_id
3450: (p_offer_assignment_id => p_rec.offer_assignment_id
3451: );
3452: --
3453: hr_utility.set_location(l_proc, 40);
3454: --
3455: if p_rec.vacancy_id is null then
3456: set_vacancy_id
3457: (p_vacancy_id => p_rec.vacancy_id
3460: );
3461: end if;
3462:
3463: --
3464: hr_utility.set_location(l_proc, 50);
3465: --
3466: chk_offers_exceeds_openings
3467: (p_vacancy_id => p_rec.vacancy_id
3468: ,p_offer_status => p_rec.offer_status
3468: ,p_offer_status => p_rec.offer_status
3469: ,p_offer_id => p_rec.offer_id
3470: );
3471: --
3472: hr_utility.set_location(l_proc, 60);
3473: --
3474: chk_respondent_id
3475: (p_respondent_id => p_rec.respondent_id
3476: ,p_offer_id => p_rec.offer_id
3476: ,p_offer_id => p_rec.offer_id
3477: ,p_object_version_number => p_rec.object_version_number
3478: );
3479: --
3480: hr_utility.set_location(l_proc, 70);
3481: --
3482: set_address_id
3483: (p_address_id => p_rec.address_id
3484: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3484: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3485: ,p_effective_date => p_effective_date
3486: );
3487: --
3488: hr_utility.set_location(l_proc, 75);
3489: --
3490: chk_address_id
3491: (p_address_id => p_rec.address_id
3492: ,p_offer_id => p_rec.offer_id
3492: ,p_offer_id => p_rec.offer_id
3493: ,p_object_version_number => p_rec.object_version_number
3494: );
3495: --
3496: hr_utility.set_location(l_proc, 80);
3497: --
3498: chk_template_id
3499: (p_template_id => p_rec.template_id
3500: ,p_offer_id => p_rec.offer_id
3501: ,p_object_version_number => p_rec.object_version_number
3502: ,p_effective_date => p_effective_date
3503: );
3504: --
3505: hr_utility.set_location(l_proc, 90);
3506: --
3507: gen_offer_version
3508: (p_offer_version => p_rec.offer_version
3509: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3508: (p_offer_version => p_rec.offer_version
3509: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3510: );
3511: --
3512: hr_utility.set_location(l_proc, 100);
3513: --
3514: chk_latest_offer
3515: (p_latest_offer => p_rec.latest_offer
3516: ,p_offer_id => p_rec.offer_id
3519: ,p_effective_date => p_effective_date
3520: ,p_object_version_number => p_rec.object_version_number
3521: );
3522: --
3523: hr_utility.set_location(l_proc, 110);
3524: --
3525: chk_offer_version_combination
3526: (p_offer_id => p_rec.offer_id
3527: ,p_offer_version => p_rec.offer_version
3528: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3529: ,p_object_version_number => p_rec.object_version_number
3530: );
3531: --
3532: hr_utility.set_location(l_proc, 120);
3533: --
3534: chk_offer_status
3535: (p_offer_id => p_rec.offer_id
3536: ,p_offer_status => p_rec.offer_status
3537: ,p_effective_date => p_effective_date
3538: ,p_object_version_number => p_rec.object_version_number
3539: );
3540: --
3541: hr_utility.set_location(l_proc, 130);
3542: --
3543: chk_offer_extended_method
3544: (p_offer_id => p_rec.offer_id
3545: ,p_offer_extended_method => p_rec.offer_extended_method
3546: ,p_effective_date => p_effective_date
3547: ,p_object_version_number => p_rec.object_version_number
3548: );
3549: --
3550: hr_utility.set_location(l_proc, 140);
3551: --
3552: chk_offer_postal_service
3553: (p_offer_id => p_rec.offer_id
3554: ,p_offer_postal_service => p_rec.offer_postal_service
3555: ,p_effective_date => p_effective_date
3556: ,p_object_version_number => p_rec.object_version_number
3557: );
3558: --
3559: hr_utility.set_location(l_proc, 150);
3560: --
3561: chk_offer_letter_file_type
3562: (p_offer_id => p_rec.offer_id
3563: ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3564: ,p_effective_date => p_effective_date
3565: ,p_object_version_number => p_rec.object_version_number
3566: );
3567: --
3568: hr_utility.set_location(l_proc, 170);
3569: --
3570: irc_iof_bus.chk_df(p_rec);
3571: --
3572: hr_utility.set_location(l_proc, 180);
3568: hr_utility.set_location(l_proc, 170);
3569: --
3570: irc_iof_bus.chk_df(p_rec);
3571: --
3572: hr_utility.set_location(l_proc, 180);
3573: --
3574: hr_utility.set_location(' Leaving:'||l_proc, 180);
3575: End insert_validate;
3576: --
3570: irc_iof_bus.chk_df(p_rec);
3571: --
3572: hr_utility.set_location(l_proc, 180);
3573: --
3574: hr_utility.set_location(' Leaving:'||l_proc, 180);
3575: End insert_validate;
3576: --
3577: -- ----------------------------------------------------------------------------
3578: -- |---------------------------< update_validate >----------------------------|
3584: --
3585: l_proc varchar2(72) := g_package||'update_validate';
3586: --
3587: Begin
3588: hr_utility.set_location('Entering:'||l_proc, 10);
3589: --
3590: -- Call all supporting business operations
3591: --
3592: hr_utility.set_location(l_proc, 20);
3588: hr_utility.set_location('Entering:'||l_proc, 10);
3589: --
3590: -- Call all supporting business operations
3591: --
3592: hr_utility.set_location(l_proc, 20);
3593: --
3594: chk_non_updateable_args
3595: (p_effective_date => p_effective_date
3596: ,p_rec => p_rec
3595: (p_effective_date => p_effective_date
3596: ,p_rec => p_rec
3597: );
3598: --
3599: hr_utility.set_location(l_proc, 30);
3600: --
3601: chk_offers_exceeds_openings
3602: (p_vacancy_id => p_rec.vacancy_id
3603: ,p_offer_status => p_rec.offer_status
3603: ,p_offer_status => p_rec.offer_status
3604: ,p_offer_id => p_rec.offer_id
3605: );
3606: --
3607: hr_utility.set_location(l_proc, 35);
3608: --
3609: chk_respondent_id
3610: (p_respondent_id => p_rec.respondent_id
3611: ,p_offer_id => p_rec.offer_id
3611: ,p_offer_id => p_rec.offer_id
3612: ,p_object_version_number => p_rec.object_version_number
3613: );
3614: --
3615: hr_utility.set_location(l_proc, 40);
3616: --
3617: chk_expiry_date
3618: (p_expiry_date => p_rec.expiry_date
3619: ,p_offer_status => p_rec.offer_status
3623: ,p_offer_shipping_date => p_rec.offer_shipping_date
3624: ,p_effective_date => p_effective_date
3625: );
3626: --
3627: hr_utility.set_location(l_proc, 45);
3628: --
3629: chk_address_id
3630: (p_address_id => p_rec.address_id
3631: ,p_offer_id => p_rec.offer_id
3631: ,p_offer_id => p_rec.offer_id
3632: ,p_object_version_number => p_rec.object_version_number
3633: );
3634: --
3635: hr_utility.set_location(l_proc, 50);
3636: --
3637: chk_template_id
3638: (p_template_id => p_rec.template_id
3639: ,p_offer_id => p_rec.offer_id
3640: ,p_object_version_number => p_rec.object_version_number
3641: ,p_effective_date => p_effective_date
3642: );
3643: --
3644: hr_utility.set_location(l_proc, 60);
3645: --
3646: chk_latest_offer
3647: (p_latest_offer => p_rec.latest_offer
3648: ,p_offer_id => p_rec.offer_id
3651: ,p_effective_date => p_effective_date
3652: ,p_object_version_number => p_rec.object_version_number
3653: );
3654: --
3655: hr_utility.set_location(l_proc, 70);
3656: --
3657: chk_offer_status
3658: (p_offer_id => p_rec.offer_id
3659: ,p_offer_status => p_rec.offer_status
3660: ,p_effective_date => p_effective_date
3661: ,p_object_version_number => p_rec.object_version_number
3662: );
3663: --
3664: hr_utility.set_location(l_proc, 80);
3665: --
3666: chk_offer_status_update
3667: (p_current_offer_record => p_rec
3668: );
3666: chk_offer_status_update
3667: (p_current_offer_record => p_rec
3668: );
3669: --
3670: hr_utility.set_location(l_proc, 90);
3671: --
3672: chk_offer_extended_method
3673: (p_offer_id => p_rec.offer_id
3674: ,p_offer_extended_method => p_rec.offer_extended_method
3675: ,p_effective_date => p_effective_date
3676: ,p_object_version_number => p_rec.object_version_number
3677: );
3678: --
3679: hr_utility.set_location(l_proc, 100);
3680: --
3681: chk_offer_postal_service
3682: (p_offer_id => p_rec.offer_id
3683: ,p_offer_postal_service => p_rec.offer_postal_service
3684: ,p_effective_date => p_effective_date
3685: ,p_object_version_number => p_rec.object_version_number
3686: );
3687: --
3688: hr_utility.set_location(l_proc, 110);
3689: --
3690: chk_offer_letter
3691: (p_offer_id => p_rec.offer_id
3692: ,p_offer_status => p_rec.offer_status
3691: (p_offer_id => p_rec.offer_id
3692: ,p_offer_status => p_rec.offer_status
3693: );
3694: --
3695: hr_utility.set_location(l_proc, 120);
3696: --
3697: chk_offer_letter_file_type
3698: (p_offer_id => p_rec.offer_id
3699: ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3700: ,p_effective_date => p_effective_date
3701: ,p_object_version_number => p_rec.object_version_number
3702: );
3703: --
3704: hr_utility.set_location(l_proc, 130);
3705: --
3706: irc_iof_bus.chk_df(p_rec);
3707: --
3708: hr_utility.set_location(' Leaving:'||l_proc, 140);
3704: hr_utility.set_location(l_proc, 130);
3705: --
3706: irc_iof_bus.chk_df(p_rec);
3707: --
3708: hr_utility.set_location(' Leaving:'||l_proc, 140);
3709: End update_validate;
3710: --
3711: -- ----------------------------------------------------------------------------
3712: -- |---------------------------< delete_validate >----------------------------|
3717: --
3718: l_proc varchar2(72) := g_package||'delete_validate';
3719: --
3720: Begin
3721: hr_utility.set_location('Entering:'||l_proc, 5);
3722: --
3723: -- Call all supporting business operations
3724: --
3725: hr_utility.set_location(' Leaving:'||l_proc, 10);
3721: hr_utility.set_location('Entering:'||l_proc, 5);
3722: --
3723: -- Call all supporting business operations
3724: --
3725: hr_utility.set_location(' Leaving:'||l_proc, 10);
3726: End delete_validate;
3727: --
3728: end irc_iof_bus;