[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 >-----------------------|
763: --
764: l_vacancy_opening_count csr_vacancy_opening_count%ROWTYPE;
765: --
766: Begin
767: hr_utility.set_location('Entering:'||l_proc,10);
768: --
769: if p_offer_status = 'CLOSED'
770: then
771: --
840: open csr_vacancy_opening_count;
841: fetch csr_vacancy_opening_count into l_vacancy_opening_count;
842: close csr_vacancy_opening_count;
843: --
844: hr_utility.set_location(l_proc,20);
845: --
846: if (l_vacancy_opening_count.budget_measurement_type <> 'FTE')
847: then
848: --
860: --
861: end if; -- no_exclusive_error
862: --
863: end if; -- if l_chk_vacancy_count check
864: hr_utility.set_location(' Leaving:'||l_proc,30);
865: exception
866: when app_exception.application_exception then
867: if hr_multi_message.exception_add
868: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
866: when app_exception.application_exception then
867: if hr_multi_message.exception_add
868: (p_associated_column1 => 'IRC_OFFERS.VACANCY_ID'
869: ) then
870: hr_utility.set_location(' Leaving:'|| l_proc, 40);
871: raise;
872: end if;
873: hr_utility.set_location(' Leaving:'|| l_proc, 50);
874: End chk_offers_exceeds_openings;
869: ) then
870: hr_utility.set_location(' Leaving:'|| l_proc, 40);
871: raise;
872: end if;
873: hr_utility.set_location(' Leaving:'|| l_proc, 50);
874: End chk_offers_exceeds_openings;
875: --
876: -- ----------------------------------------------------------------------------
877: -- |--------------------------< chk_respondent_id >---------------------------|
912: from fnd_user
913: where user_id = p_respondent_id;
914: --
915: Begin
916: hr_utility.set_location('Entering:'||l_proc,10);
917: --
918: if hr_multi_message.no_exclusive_error(
919: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
920: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
928: l_api_updating := irc_iof_shd.api_updating
929: (p_offer_id => p_offer_id
930: ,p_object_version_number => p_object_version_number);
931: --
932: hr_utility.set_location(l_proc,20);
933:
934: if ((l_api_updating
935: and nvl(irc_iof_shd.g_old_rec.respondent_id, hr_api.g_number) <>
936: nvl(p_respondent_id, hr_api.g_number))
940: -- Check if respondent id is not null.
941: --
942: if p_respondent_id is not null then
943:
944: hr_utility.set_location(l_proc, 30);
945:
946: open csr_respondent_id;
947: fetch csr_respondent_id into l_respondent_id;
948: --
945:
946: open csr_respondent_id;
947: fetch csr_respondent_id into l_respondent_id;
948: --
949: hr_utility.set_location(l_proc,40);
950: if (csr_respondent_id%notfound)
951: then
952: close csr_respondent_id;
953: fnd_message.set_name('FND','FND_GRANTS_GNT_USER_INVALID');
954: fnd_message.raise_error;
955: end if;
956: close csr_respondent_id;
957: --
958: hr_utility.set_location(' Leaving:'||l_proc,50);
959: --
960: end if;
961: end if;
962: end if; -- no_exclusive_error
965: when app_exception.application_exception then
966: if hr_multi_message.exception_add
967: (p_associated_column1 => 'IRC_OFFERS.RESPONDENT_ID'
968: ) then
969: hr_utility.set_location(' Leaving:'|| l_proc, 60);
970: raise;
971: end if;
972: hr_utility.set_location(' Leaving:'|| l_proc, 70);
973: End chk_respondent_id;
968: ) then
969: hr_utility.set_location(' Leaving:'|| l_proc, 60);
970: raise;
971: end if;
972: hr_utility.set_location(' Leaving:'|| l_proc, 70);
973: End chk_respondent_id;
974: --
975: -- ----------------------------------------------------------------------------
976: -- |---------------------------< chk_expiry_date >----------------------------|
1019: l_prev_offer_shipping_date irc_offers.offer_shipping_date%TYPE := irc_iof_shd.g_old_rec.offer_shipping_date;
1020: l_effective_date date;
1021: --
1022: Begin
1023: hr_utility.set_location('Entering:'||l_proc,10);
1024: --
1025: if hr_multi_message.no_exclusive_error(
1026: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1027: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1030: --
1031: if p_offer_status = 'EXTENDED'
1032: then
1033: --
1034: hr_utility.set_location(l_proc, 20);
1035: --
1036: if p_expiry_date is not null
1037: then
1038: --
1064: --
1065: if l_offer_duration_value is not null
1066: then
1067: --
1068: hr_utility.set_location(l_proc, 30);
1069: --
1070: -- Set the effective date
1071: --
1072: if l_prev_expiry_date > p_effective_date
1082: --
1083: if l_offer_duration_measurement = 'MONTH'
1084: then
1085: --
1086: hr_utility.set_location(l_proc, 60);
1087: --
1088: l_expiry_date := add_months(l_effective_date,l_offer_duration_value);
1089: --
1090: elsif l_offer_duration_measurement = 'WEEK'
1089: --
1090: elsif l_offer_duration_measurement = 'WEEK'
1091: then
1092: --
1093: hr_utility.set_location(l_proc, 50);
1094: --
1095: l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1096: --
1097: else -- By default l_offer_duration_measurement = 'DAY'
1095: l_expiry_date := l_effective_date + (l_offer_duration_value * 7);
1096: --
1097: else -- By default l_offer_duration_measurement = 'DAY'
1098: --
1099: hr_utility.set_location(l_proc, 40);
1100: --
1101: l_expiry_date := l_effective_date + l_offer_duration_value;
1102: --
1103: end if;
1122: -- Set the in out variable
1123: --
1124: p_expiry_date := l_expiry_date;
1125: --
1126: hr_utility.set_location(' Leaving:'||l_proc,70);
1127: --
1128: exception
1129: when app_exception.application_exception then
1130: if hr_multi_message.exception_add
1129: when app_exception.application_exception then
1130: if hr_multi_message.exception_add
1131: (p_associated_column1 => 'IRC_OFFERS.EXPIRY_DATE'
1132: ) then
1133: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1134: raise;
1135: end if;
1136: hr_utility.set_location(' Leaving:'|| l_proc, 90);
1137: End chk_expiry_date;
1132: ) then
1133: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1134: raise;
1135: end if;
1136: hr_utility.set_location(' Leaving:'|| l_proc, 90);
1137: End chk_expiry_date;
1138: --
1139: -- ----------------------------------------------------------------------------
1140: -- |----------------------------< set_address_id >----------------------------|
1185: between adr.date_from
1186: and nvl(adr.date_to, trunc(sysdate));
1187: --
1188: Begin
1189: hr_utility.set_location('Entering:'||l_proc,10);
1190: --
1191: if hr_multi_message.no_exclusive_error(
1192: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1193: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1193: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1194: ,p_check_column3 => 'IRC_OFFERS.VACANCY_ID'
1195: ) then
1196: --
1197: hr_utility.set_location(l_proc,20);
1198: --
1199: -- Default the address_id only if it is null
1200: --
1201: if p_address_id is null then
1202: --
1203: open csr_rec_address_id;
1204: fetch csr_rec_address_id into l_rec_address_id;
1205: --
1206: hr_utility.set_location(l_proc,30);
1207: if (csr_rec_address_id%found)
1208: then
1209: --
1210: -- The person has a recruiting address. Hence, default the address_id
1214: --
1215: end if;
1216: close csr_rec_address_id;
1217: --
1218: hr_utility.set_location(' Leaving:'||l_proc,40);
1219: end if;
1220: end if; -- no_exclusive_error
1221: --
1222: exception
1223: when app_exception.application_exception then
1224: if hr_multi_message.exception_add
1225: (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1226: ) then
1227: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1228: raise;
1229: end if;
1230: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1231: End set_address_id;
1226: ) then
1227: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1228: raise;
1229: end if;
1230: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1231: End set_address_id;
1232: --
1233: -- ----------------------------------------------------------------------------
1234: -- |----------------------------< chk_address_id >----------------------------|
1269: from per_addresses
1270: where address_id = p_address_id;
1271: --
1272: Begin
1273: hr_utility.set_location('Entering:'||l_proc,10);
1274: --
1275: if hr_multi_message.no_exclusive_error(
1276: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1277: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1285: l_api_updating := irc_iof_shd.api_updating
1286: (p_offer_id => p_offer_id
1287: ,p_object_version_number => p_object_version_number);
1288: --
1289: hr_utility.set_location(l_proc,20);
1290:
1291: if ((l_api_updating
1292: and nvl(irc_iof_shd.g_old_rec.address_id, hr_api.g_number) <>
1293: nvl(p_address_id, hr_api.g_number))
1300: --
1301: open csr_address_id;
1302: fetch csr_address_id into l_address_id;
1303: --
1304: hr_utility.set_location(l_proc,30);
1305: if (csr_address_id%notfound)
1306: then
1307: close csr_address_id;
1308: fnd_message.set_name('PER','IRC_412001_BAD_ADDRESS_ID');
1309: fnd_message.raise_error;
1310: end if;
1311: close csr_address_id;
1312: --
1313: hr_utility.set_location(' Leaving:'||l_proc,40);
1314: end if;
1315: end if;
1316: end if; -- no_exclusive_error
1317: --
1319: when app_exception.application_exception then
1320: if hr_multi_message.exception_add
1321: (p_associated_column1 => 'IRC_OFFERS.ADDRESS_ID'
1322: ) then
1323: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1324: raise;
1325: end if;
1326: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1327: End chk_address_id;
1322: ) then
1323: hr_utility.set_location(' Leaving:'|| l_proc, 50);
1324: raise;
1325: end if;
1326: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1327: End chk_address_id;
1328: --
1329: -- ----------------------------------------------------------------------------
1330: -- |----------------------------< chk_template_id >---------------------------|
1403: and iof.offer_assignment_id = ppaf.assignment_id
1404: and ita.organization_id = ppaf.organization_id;
1405: --
1406: Begin
1407: hr_utility.set_location('Entering:'||l_proc,10);
1408: --
1409: if hr_multi_message.no_exclusive_error(
1410: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1411: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1419: l_api_updating := irc_iof_shd.api_updating
1420: (p_offer_id => p_offer_id
1421: ,p_object_version_number => p_object_version_number);
1422: --
1423: hr_utility.set_location(l_proc,20);
1424:
1425: if ((l_api_updating
1426: and nvl(irc_iof_shd.g_old_rec.template_id, hr_api.g_number) <>
1427: nvl(p_template_id, hr_api.g_number)) or
1430: -- Check if template id is not null and if it is active
1431: --
1432: if p_template_id is not null then
1433: --
1434: hr_utility.set_location(l_proc,30);
1435: --
1436: open csr_template_id;
1437: fetch csr_template_id into l_template_id;
1438: --
1438: --
1439: if (csr_template_id%notfound)
1440: then
1441: --
1442: hr_utility.set_location(l_proc,40);
1443: --
1444: close csr_template_id;
1445: fnd_message.set_name('PER','IRC_412326_OFFER_INV_TEMPLT_ID');
1446: fnd_message.raise_error;
1448: close csr_template_id;
1449: --
1450: else
1451: --
1452: hr_utility.set_location(l_proc,50);
1453: --
1454: open csr_default_template_job;
1455: fetch csr_default_template_job into l_template_id;
1456: --
1456: --
1457: if (csr_default_template_job%notfound)
1458: then
1459: --
1460: hr_utility.set_location(l_proc,60);
1461: --
1462: close csr_default_template_job;
1463: --
1464: open csr_default_template_pos;
1466: --
1467: if (csr_default_template_pos%notfound)
1468: then
1469: --
1470: hr_utility.set_location(l_proc,70);
1471: --
1472: close csr_default_template_pos;
1473: --
1474: open csr_default_template_org;
1476: --
1477: if (csr_default_template_org%notfound)
1478: then
1479: --
1480: hr_utility.set_location(l_proc,80);
1481: --
1482: close csr_default_template_org;
1483: l_template_id := p_template_id;
1484: --
1488: --
1489: end if; -- if - else - endif;
1490: end if; -- l_api_updating
1491: end if; -- no_exclusive_error
1492: hr_utility.set_location(' Leaving:'||l_proc,90);
1493: --
1494: exception
1495: when app_exception.application_exception then
1496: if hr_multi_message.exception_add
1495: when app_exception.application_exception then
1496: if hr_multi_message.exception_add
1497: (p_associated_column1 => 'IRC_OFFERS.TEMPLATE_ID'
1498: ) then
1499: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1500: raise;
1501: end if;
1502: hr_utility.set_location(' Leaving:'|| l_proc, 110);
1503: End chk_template_id;
1498: ) then
1499: hr_utility.set_location(' Leaving:'|| l_proc, 100);
1500: raise;
1501: end if;
1502: hr_utility.set_location(' Leaving:'|| l_proc, 110);
1503: End chk_template_id;
1504: --
1505: -- ----------------------------------------------------------------------------
1506: -- |--------------------------< gen_offer_version >---------------------------|
1535: from irc_offers
1536: where applicant_assignment_id = p_applicant_assignment_id;
1537: --
1538: Begin
1539: hr_utility.set_location('Entering:'||l_proc,10);
1540: --
1541: hr_api.mandatory_arg_error
1542: (p_api_name => l_proc
1543: ,p_argument => 'APPLICANT_ASSIGNMENT_ID'
1551: fetch csr_get_offer_version into l_offer_version;
1552: close csr_get_offer_version;
1553: p_offer_version := l_offer_version;
1554: --
1555: hr_utility.set_location(' Leaving:'||l_proc,20);
1556: --
1557: exception
1558: when app_exception.application_exception then
1559: if hr_multi_message.exception_add
1558: when app_exception.application_exception then
1559: if hr_multi_message.exception_add
1560: (p_associated_column1 => 'IRC_OFFERS.OFFER_VERSION'
1561: ) then
1562: hr_utility.set_location(' Leaving:'|| l_proc, 30);
1563: raise;
1564: end if;
1565: hr_utility.set_location(' Leaving:'|| l_proc, 40);
1566: End gen_offer_version;
1561: ) then
1562: hr_utility.set_location(' Leaving:'|| l_proc, 30);
1563: raise;
1564: end if;
1565: hr_utility.set_location(' Leaving:'|| l_proc, 40);
1566: End gen_offer_version;
1567: --
1568: -- ----------------------------------------------------------------------------
1569: -- |--------------------------< chk_latest_offer >---------------------------|
1640: where latest_offer = 'Y'
1641: and applicant_assignment_id = p_applicant_assignment_id;
1642: --
1643: Begin
1644: hr_utility.set_location('Entering:'||l_proc,10);
1645: --
1646: if hr_multi_message.no_exclusive_error(
1647: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1648: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1656: l_api_updating := irc_iof_shd.api_updating
1657: (p_offer_id => p_offer_id
1658: ,p_object_version_number => p_object_version_number);
1659: --
1660: hr_utility.set_location(l_proc,20);
1661: --
1662: -- If a newly created offer is in SFL status, there
1663: -- should not be a validation as the offer_status for this
1664: -- offer will always be 'N'.
1670: --
1671: open csr_latest_offer_ins;
1672: fetch csr_latest_offer_ins into l_latest_offer;
1673: --
1674: hr_utility.set_location(l_proc,30);
1675: if (csr_latest_offer_ins%found)
1676: then
1677: close csr_latest_offer_ins;
1678: fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1691: --
1692: open csr_latest_offer_upd;
1693: fetch csr_latest_offer_upd into l_latest_offer;
1694: --
1695: hr_utility.set_location(l_proc,30);
1696: if (csr_latest_offer_upd%found)
1697: then
1698: close csr_latest_offer_upd;
1699: fnd_message.set_name('PER','IRC_412332_INV_APL_LSTOFR_COMB');
1709: and nvl(irc_iof_shd.g_old_rec.latest_offer, hr_api.g_varchar2) <>
1710: nvl(p_latest_offer, hr_api.g_varchar2))
1711: or (NOT l_api_updating)) then
1712: --
1713: hr_utility.set_location(l_proc,40);
1714: --
1715: -- Checks that the value for latest_offer is
1716: -- valid and exists on YES_NO lookup
1717: --
1722: ) then
1723: --
1724: -- Error: Invalid latest offer value.
1725: --
1726: hr_utility.set_location(l_proc,50);
1727: --
1728: fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1729: fnd_message.raise_error;
1730: end if;
1728: fnd_message.set_name(800, 'IRC_412307_INV_LATEST_OFR_VAL');
1729: fnd_message.raise_error;
1730: end if;
1731: --
1732: hr_utility.set_location(' Leaving:'||l_proc,60);
1733: end if;
1734: end if; -- no_exclusive_error
1735: --
1736: exception
1737: when app_exception.application_exception then
1738: if hr_multi_message.exception_add
1739: (p_associated_column1 => 'IRC_OFFERS.LATEST_OFFER'
1740: ) then
1741: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1742: raise;
1743: end if;
1744: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1745: End chk_latest_offer;
1740: ) then
1741: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1742: raise;
1743: end if;
1744: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1745: End chk_latest_offer;
1746: --
1747: -- ----------------------------------------------------------------------------
1748: -- |--------------------< chk_offer_version_combination >---------------------|
1789: and applicant_assignment_id = p_applicant_assignment_id;
1790: --
1791: Begin
1792: --
1793: hr_utility.set_location(' Entering:'||l_proc,10);
1794: --
1795: if hr_multi_message.no_exclusive_error(
1796: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1797: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1817: (NOT l_api_updating)) then
1818: --
1819: open csr_version;
1820: fetch csr_version into l_version;
1821: hr_utility.set_location(l_proc,20);
1822: if csr_version%found then
1823: close csr_version;
1824: fnd_message.set_name(800,'IRC_412308_INV_OFFER_VER_COMB');
1825: fnd_message.raise_error;
1833: if hr_multi_message.exception_add
1834: (p_associated_column1 => 'IRC_OFFER.OFFER_VERSION'
1835: ,p_associated_column2 => 'IRC_OFFER.APPLICANT_ASSIGNMENT_ID'
1836: ) then
1837: hr_utility.set_location(' Leaving:'||l_proc,30);
1838: raise;
1839: end if;
1840: --
1841: hr_utility.set_location(' Leaving:'||l_proc,40);
1837: hr_utility.set_location(' Leaving:'||l_proc,30);
1838: raise;
1839: end if;
1840: --
1841: hr_utility.set_location(' Leaving:'||l_proc,40);
1842: End chk_offer_version_combination;
1843: --
1844: -- ---------------------------------------------------------------------------
1845: -- |--------------------------< chk_offer_status >---------------------------|
1878: l_proc varchar2(72) := g_package||'chk_offer_status';
1879: l_api_updating boolean;
1880: --
1881: begin
1882: hr_utility.set_location('Entering:'|| l_proc, 10);
1883: --
1884: if hr_multi_message.no_exclusive_error(
1885: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
1886: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
1909: nvl(p_offer_status, hr_api.g_varchar2))
1910: or
1911: (NOT l_api_updating)) then
1912: --
1913: hr_utility.set_location(l_proc, 20);
1914: --
1915: -- Checks that the value for offer_status is
1916: -- valid and exists on irc_offer_statuses within
1917: -- the specified date range
1921: ,p_lookup_type => 'IRC_OFFER_STATUSES'
1922: ,p_lookup_code => p_offer_status
1923: ) then
1924: --
1925: hr_utility.set_location(l_proc, 30);
1926: -- Error: Invalid offer status type.
1927: fnd_message.set_name('PER', 'IRC_412323_INV_OFFER_STATUS');
1928: fnd_message.raise_error;
1929: end if;
1934: --
1935: if NOT l_api_updating
1936: then
1937: --
1938: hr_utility.set_location(l_proc, 40);
1939: if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1940: then
1941: --
1942: hr_utility.set_location(l_proc, 50);
1938: hr_utility.set_location(l_proc, 40);
1939: if p_offer_status in ('CLOSED', 'EXTENDED', 'HOLD', 'CORRECTION', 'PENDING_EXTENDED')
1940: then
1941: --
1942: hr_utility.set_location(l_proc, 50);
1943: fnd_message.set_name('PER', 'IRC_412309_INV_CRT_OFR_STATUS');
1944: fnd_message.raise_error;
1945: --
1946: end if;
1948: --
1949: --
1950: end if; -- no_exclusive_error
1951: --
1952: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1953: exception
1954: when app_exception.application_exception then
1955: if hr_multi_message.exception_add
1956: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1954: when app_exception.application_exception then
1955: if hr_multi_message.exception_add
1956: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
1957: ) then
1958: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1959: raise;
1960: end if;
1961: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1962: end chk_offer_status;
1957: ) then
1958: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1959: raise;
1960: end if;
1961: hr_utility.set_location(' Leaving:'|| l_proc, 80);
1962: end chk_offer_status;
1963: --
1964: --
1965: -- ---------------------------------------------------------------------------
2009: where ios2.offer_id = p_current_offer_record.offer_id
2010: and ios2.offer_status_history_id > ios1.offer_status_history_id);
2011: --
2012: begin
2013: hr_utility.set_location('Entering:'|| l_proc, 10);
2014: --
2015: if p_current_offer_record.offer_status <> 'CLOSED'
2016: and p_current_offer_record.offer_status <> 'APPROVED'
2017: and p_current_offer_record.offer_status <> 'CORRECTION'
2017: and p_current_offer_record.offer_status <> 'CORRECTION'
2018: and p_current_offer_record.offer_status <> 'EXTENDED'
2019: then
2020: --
2021: hr_utility.set_location(l_proc,20);
2022: --
2023: -- Check to see if the offer status is 'Hold'.
2024: --
2025: if ( p_current_offer_record.offer_status = 'HOLD'
2024: --
2025: if ( p_current_offer_record.offer_status = 'HOLD'
2026: ) then
2027: --
2028: hr_utility.set_location(l_proc,30);
2029: --
2030: -- Check if the offer was previously in 'Hold'
2031: -- state too. If so, the offer has been updated. Hence,
2032: -- throw an error saying that the offer cannot be updated.
2033: --
2034: if ( l_prev_offer_status = 'HOLD'
2035: ) then
2036: --
2037: hr_utility.set_location(l_proc,40);
2038: --
2039: fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2040: fnd_message.raise_error;
2041: end if;
2094: );
2095: if ( l_mutiple_fields_updated = true )
2096: then
2097: --
2098: hr_utility.set_location(l_proc,45);
2099: --
2100: fnd_message.set_name('PER','IRC_412306_CANT_UPD_HELD_OFFER');
2101: fnd_message.raise_error;
2102: end if;
2104: else
2105: --
2106: -- If the offer status is anything else.
2107: --
2108: hr_utility.set_location(l_proc,50);
2109: --
2110: -- Check if the offer was previously in 'Hold' State.
2111: -- If so, the current state should be the state which existed
2112: -- before the offer was Held.
2113: --
2114: if ( l_prev_offer_status = 'HOLD'
2115: ) then
2116: --
2117: hr_utility.set_location(l_proc,60);
2118: --
2119: open csr_prev_to_prev_offer_status;
2120: fetch csr_prev_to_prev_offer_status into l_prev_to_prev_offer_status;
2121: close csr_prev_to_prev_offer_status;
2122: --
2123: if ( p_current_offer_record.offer_status <> l_prev_to_prev_offer_status
2124: ) then
2125: --
2126: hr_utility.set_location(l_proc,70);
2127: --
2128: fnd_message.set_name('PER','IRC_412305_INV_PREVTOPREV_OFR');
2129: fnd_message.raise_error;
2130: --
2132: end if;
2133: end if; -- if-else-end if
2134: end if; -- Offer_status = 'CLOSED'
2135: --
2136: hr_utility.set_location(' Leaving:'|| l_proc, 80);
2137: exception
2138: when app_exception.application_exception then
2139: if hr_multi_message.exception_add
2140: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2138: when app_exception.application_exception then
2139: if hr_multi_message.exception_add
2140: (p_associated_column1 => 'IRC_OFFERS.OFFER_STATUS'
2141: ) then
2142: hr_utility.set_location(' Leaving:'|| l_proc, 90);
2143: raise;
2144: end if;
2145: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2146: end chk_offer_status_update;
2141: ) then
2142: hr_utility.set_location(' Leaving:'|| l_proc, 90);
2143: raise;
2144: end if;
2145: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2146: end chk_offer_status_update;
2147: --
2148: -- ----------------------------------------------------------------------------
2149: -- |---------------------< chk_multiple_fields_updated >----------------------|
2281: --
2282: l_offer_old_rec C_Sel1%ROWTYPE;
2283: Begin
2284: --
2285: hr_utility.set_location('Entering:'||l_proc,10);
2286: --
2287: Open C_Sel1;
2288: Fetch C_Sel1 Into l_offer_old_rec;
2289: If C_Sel1%notfound Then
2303: nvl(l_offer_old_rec.offer_id, hr_api.g_number) <>
2304: nvl(p_offer_id, hr_api.g_number)
2305: then
2306: --
2307: hr_utility.set_location(l_proc,20);
2308: --
2309: l_update_count := l_update_count + 1;
2310: end if;
2311: end if;
2311: end if;
2312:
2313: if(p_offer_status <> hr_api.g_varchar2) then
2314: --
2315: hr_utility.set_location(l_proc,30);
2316: --
2317: if
2318: nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2319: nvl(p_offer_status, hr_api.g_varchar2)
2318: nvl(l_offer_old_rec.offer_status, hr_api.g_varchar2) <>
2319: nvl(p_offer_status, hr_api.g_varchar2)
2320: then
2321: --
2322: hr_utility.set_location(l_proc,50);
2323: --
2324: l_update_count := l_update_count + 1;
2325: end if;
2326: end if;
2332: nvl(l_offer_old_rec.discretionary_job_title, hr_api.g_varchar2) <>
2333: nvl(p_discretionary_job_title, hr_api.g_varchar2)
2334: then
2335: --
2336: hr_utility.set_location(l_proc,60);
2337: --
2338: l_update_count := l_update_count + 1;
2339: end if;
2340: end if;
2346: nvl(l_offer_old_rec.offer_extended_method, hr_api.g_varchar2) <>
2347: nvl(p_offer_extended_method, hr_api.g_varchar2)
2348: then
2349: --
2350: hr_utility.set_location(l_proc,70);
2351: --
2352: l_update_count := l_update_count + 1;
2353: end if;
2354: end if;
2360: nvl(l_offer_old_rec.expiry_date, hr_api.g_date) <>
2361: nvl(p_expiry_date, hr_api.g_date)
2362: then
2363: --
2364: hr_utility.set_location(l_proc,90);
2365: --
2366: l_update_count := l_update_count + 1;
2367: end if;
2368: end if;
2374: nvl(l_offer_old_rec.proposed_start_date, hr_api.g_date) <>
2375: nvl(p_proposed_start_date, hr_api.g_date)
2376: then
2377: --
2378: hr_utility.set_location(l_proc,100);
2379: --
2380: l_update_count := l_update_count + 1;
2381: end if;
2382: end if;
2388: nvl(l_offer_old_rec.offer_letter_tracking_code, hr_api.g_varchar2) <>
2389: nvl(p_offer_letter_tracking_code, hr_api.g_varchar2)
2390: then
2391: --
2392: hr_utility.set_location(l_proc,110);
2393: --
2394: l_update_count := l_update_count + 1;
2395: end if;
2396: end if;
2402: nvl(l_offer_old_rec.offer_postal_service, hr_api.g_varchar2) <>
2403: nvl(p_offer_postal_service, hr_api.g_varchar2)
2404: then
2405: --
2406: hr_utility.set_location(l_proc,120);
2407: --
2408: l_update_count := l_update_count + 1;
2409: end if;
2410: end if;
2416: nvl(l_offer_old_rec.offer_shipping_date, hr_api.g_date) <>
2417: nvl(p_offer_shipping_date, hr_api.g_date)
2418: then
2419: --
2420: hr_utility.set_location(l_proc,130);
2421: --
2422: l_update_count := l_update_count + 1;
2423: end if;
2424: end if;
2429: nvl(l_offer_old_rec.applicant_assignment_id, hr_api.g_number) <>
2430: nvl(p_applicant_assignment_id, hr_api.g_number)
2431: then
2432: --
2433: hr_utility.set_location(l_proc,150);
2434: --
2435: l_update_count := l_update_count + 1;
2436: end if;
2437: end if;
2441: nvl(l_offer_old_rec.offer_assignment_id, hr_api.g_number) <>
2442: nvl(p_offer_assignment_id, hr_api.g_number)
2443: then
2444: --
2445: hr_utility.set_location(l_proc,160);
2446: --
2447: l_update_count := l_update_count + 1;
2448: end if;
2449: end if;
2455: nvl(l_offer_old_rec.address_id, hr_api.g_number) <>
2456: nvl(p_address_id, hr_api.g_number)
2457: then
2458: --
2459: hr_utility.set_location(l_proc,170);
2460: --
2461: l_update_count := l_update_count + 1;
2462: end if;
2463: end if;
2469: nvl(l_offer_old_rec.template_id, hr_api.g_number) <>
2470: nvl(p_template_id, hr_api.g_number)
2471: then
2472: --
2473: hr_utility.set_location(l_proc,180);
2474: --
2475: l_update_count := l_update_count + 1;
2476: end if;
2477: end if;
2483: nvl(l_offer_old_rec.offer_letter_file_type, hr_api.g_varchar2) <>
2484: nvl(p_offer_letter_file_type, hr_api.g_varchar2)
2485: then
2486: --
2487: hr_utility.set_location(l_proc,190);
2488: --
2489: l_update_count := l_update_count + 1;
2490: end if;
2491: end if;
2497: nvl(l_offer_old_rec.offer_letter_file_name, hr_api.g_varchar2) <>
2498: nvl(p_offer_letter_file_name, hr_api.g_varchar2)
2499: then
2500: --
2501: hr_utility.set_location(l_proc,200);
2502: --
2503: l_update_count := l_update_count + 1;
2504: end if;
2505: end if;
2511: nvl(l_offer_old_rec.attribute_category, hr_api.g_varchar2) <>
2512: nvl(p_attribute_category, hr_api.g_varchar2)
2513: then
2514: --
2515: hr_utility.set_location(l_proc,210);
2516: --
2517: l_update_count := l_update_count + 1;
2518: end if;
2519: end if;
2525: nvl(l_offer_old_rec.attribute1, hr_api.g_varchar2) <>
2526: nvl(p_attribute1, hr_api.g_varchar2)
2527: then
2528: --
2529: hr_utility.set_location(l_proc,220);
2530: --
2531: l_update_count := l_update_count + 1;
2532: end if;
2533: end if;
2539: nvl(l_offer_old_rec.attribute2, hr_api.g_varchar2) <>
2540: nvl(p_attribute2, hr_api.g_varchar2)
2541: then
2542: --
2543: hr_utility.set_location(l_proc,230);
2544: --
2545: l_update_count := l_update_count + 1;
2546: end if;
2547: end if;
2553: nvl(l_offer_old_rec.attribute3, hr_api.g_varchar2) <>
2554: nvl(p_attribute3, hr_api.g_varchar2)
2555: then
2556: --
2557: hr_utility.set_location(l_proc,240);
2558: --
2559: l_update_count := l_update_count + 1;
2560: end if;
2561: end if;
2567: nvl(l_offer_old_rec.attribute4, hr_api.g_varchar2) <>
2568: nvl(p_attribute4, hr_api.g_varchar2)
2569: then
2570: --
2571: hr_utility.set_location(l_proc,250);
2572: --
2573: l_update_count := l_update_count + 1;
2574: end if;
2575: end if;
2581: nvl(l_offer_old_rec.attribute5, hr_api.g_varchar2) <>
2582: nvl(p_attribute5, hr_api.g_varchar2)
2583: then
2584: --
2585: hr_utility.set_location(l_proc,260);
2586: --
2587: l_update_count := l_update_count + 1;
2588: end if;
2589: end if;
2595: nvl(l_offer_old_rec.attribute6, hr_api.g_varchar2) <>
2596: nvl(p_attribute6, hr_api.g_varchar2)
2597: then
2598: --
2599: hr_utility.set_location(l_proc,270);
2600: --
2601: l_update_count := l_update_count + 1;
2602: end if;
2603: end if;
2609: nvl(l_offer_old_rec.attribute7, hr_api.g_varchar2) <>
2610: nvl(p_attribute7, hr_api.g_varchar2)
2611: then
2612: --
2613: hr_utility.set_location(l_proc,280);
2614: --
2615: l_update_count := l_update_count + 1;
2616: end if;
2617: end if;
2623: nvl(l_offer_old_rec.attribute8, hr_api.g_varchar2) <>
2624: nvl(p_attribute8, hr_api.g_varchar2)
2625: then
2626: --
2627: hr_utility.set_location(l_proc,290);
2628: --
2629: l_update_count := l_update_count + 1;
2630: end if;
2631: end if;
2637: nvl(l_offer_old_rec.attribute9, hr_api.g_varchar2) <>
2638: nvl(p_attribute9, hr_api.g_varchar2)
2639: then
2640: --
2641: hr_utility.set_location(l_proc,300);
2642: --
2643: l_update_count := l_update_count + 1;
2644: end if;
2645: end if;
2651: nvl(l_offer_old_rec.attribute10, hr_api.g_varchar2) <>
2652: nvl(p_attribute10, hr_api.g_varchar2)
2653: then
2654: --
2655: hr_utility.set_location(l_proc,310);
2656: --
2657: l_update_count := l_update_count + 1;
2658: end if;
2659: end if;
2665: nvl(l_offer_old_rec.attribute11, hr_api.g_varchar2) <>
2666: nvl(p_attribute11, hr_api.g_varchar2)
2667: then
2668: --
2669: hr_utility.set_location(l_proc,320);
2670: --
2671: l_update_count := l_update_count + 1;
2672: end if;
2673: end if;
2679: nvl(l_offer_old_rec.attribute12, hr_api.g_varchar2) <>
2680: nvl(p_attribute12, hr_api.g_varchar2)
2681: then
2682: --
2683: hr_utility.set_location(l_proc,330);
2684: --
2685: l_update_count := l_update_count + 1;
2686: end if;
2687: end if;
2693: nvl(l_offer_old_rec.attribute13, hr_api.g_varchar2) <>
2694: nvl(p_attribute13, hr_api.g_varchar2)
2695: then
2696: --
2697: hr_utility.set_location(l_proc,340);
2698: --
2699: l_update_count := l_update_count + 1;
2700: end if;
2701: end if;
2707: nvl(l_offer_old_rec.attribute14, hr_api.g_varchar2) <>
2708: nvl(p_attribute14, hr_api.g_varchar2)
2709: then
2710: --
2711: hr_utility.set_location(l_proc,350);
2712: --
2713: l_update_count := l_update_count + 1;
2714: end if;
2715: end if;
2721: nvl(l_offer_old_rec.attribute15, hr_api.g_varchar2) <>
2722: nvl(p_attribute15, hr_api.g_varchar2)
2723: then
2724: --
2725: hr_utility.set_location(l_proc,360);
2726: --
2727: l_update_count := l_update_count + 1;
2728: end if;
2729: end if;
2735: nvl(l_offer_old_rec.attribute16, hr_api.g_varchar2) <>
2736: nvl(p_attribute16, hr_api.g_varchar2)
2737: then
2738: --
2739: hr_utility.set_location(l_proc,370);
2740: --
2741: l_update_count := l_update_count + 1;
2742: end if;
2743: end if;
2749: nvl(l_offer_old_rec.attribute17, hr_api.g_varchar2) <>
2750: nvl(p_attribute17, hr_api.g_varchar2)
2751: then
2752: --
2753: hr_utility.set_location(l_proc,380);
2754: --
2755: l_update_count := l_update_count + 1;
2756: end if;
2757: end if;
2763: nvl(l_offer_old_rec.attribute18, hr_api.g_varchar2) <>
2764: nvl(p_attribute18, hr_api.g_varchar2)
2765: then
2766: --
2767: hr_utility.set_location(l_proc,390);
2768: --
2769: l_update_count := l_update_count + 1;
2770: end if;
2771: end if;
2777: nvl(l_offer_old_rec.attribute19, hr_api.g_varchar2) <>
2778: nvl(p_attribute19, hr_api.g_varchar2)
2779: then
2780: --
2781: hr_utility.set_location(l_proc,400);
2782: --
2783: l_update_count := l_update_count + 1;
2784: end if;
2785: end if;
2791: nvl(l_offer_old_rec.attribute20, hr_api.g_varchar2) <>
2792: nvl(p_attribute20, hr_api.g_varchar2)
2793: then
2794: --
2795: hr_utility.set_location(l_proc,410);
2796: --
2797: l_update_count := l_update_count + 1;
2798: end if;
2799: end if;
2805: nvl(l_offer_old_rec.attribute21, hr_api.g_varchar2) <>
2806: nvl(p_attribute21, hr_api.g_varchar2)
2807: then
2808: --
2809: hr_utility.set_location(l_proc,420);
2810: --
2811: l_update_count := l_update_count + 1;
2812: end if;
2813: end if;
2819: nvl(l_offer_old_rec.attribute22, hr_api.g_varchar2) <>
2820: nvl(p_attribute22, hr_api.g_varchar2)
2821: then
2822: --
2823: hr_utility.set_location(l_proc,430);
2824: --
2825: l_update_count := l_update_count + 1;
2826: end if;
2827: end if;
2833: nvl(l_offer_old_rec.attribute23, hr_api.g_varchar2) <>
2834: nvl(p_attribute23, hr_api.g_varchar2)
2835: then
2836: --
2837: hr_utility.set_location(l_proc,440);
2838: --
2839: l_update_count := l_update_count + 1;
2840: end if;
2841: end if;
2847: nvl(l_offer_old_rec.attribute24, hr_api.g_varchar2) <>
2848: nvl(p_attribute24, hr_api.g_varchar2)
2849: then
2850: --
2851: hr_utility.set_location(l_proc,450);
2852: --
2853: l_update_count := l_update_count + 1;
2854: end if;
2855: end if;
2861: nvl(l_offer_old_rec.attribute25, hr_api.g_varchar2) <>
2862: nvl(p_attribute25, hr_api.g_varchar2)
2863: then
2864: --
2865: hr_utility.set_location(l_proc,460);
2866: --
2867: l_update_count := l_update_count + 1;
2868: end if;
2869: end if;
2875: nvl(l_offer_old_rec.attribute26, hr_api.g_varchar2) <>
2876: nvl(p_attribute26, hr_api.g_varchar2)
2877: then
2878: --
2879: hr_utility.set_location(l_proc,470);
2880: --
2881: l_update_count := l_update_count + 1;
2882: end if;
2883: end if;
2889: nvl(l_offer_old_rec.attribute27, hr_api.g_varchar2) <>
2890: nvl(p_attribute27, hr_api.g_varchar2)
2891: then
2892: --
2893: hr_utility.set_location(l_proc,480);
2894: --
2895: l_update_count := l_update_count + 1;
2896: end if;
2897: end if;
2903: nvl(l_offer_old_rec.attribute28, hr_api.g_varchar2) <>
2904: nvl(p_attribute28, hr_api.g_varchar2)
2905: then
2906: --
2907: hr_utility.set_location(l_proc,490);
2908: --
2909: l_update_count := l_update_count + 1;
2910: end if;
2911: end if;
2917: nvl(l_offer_old_rec.attribute29, hr_api.g_varchar2) <>
2918: nvl(p_attribute29, hr_api.g_varchar2)
2919: then
2920: --
2921: hr_utility.set_location(l_proc,500);
2922: --
2923: l_update_count := l_update_count + 1;
2924: end if;
2925: end if;
2931: nvl(l_offer_old_rec.attribute30, hr_api.g_varchar2) <>
2932: nvl(p_attribute30, hr_api.g_varchar2)
2933: then
2934: --
2935: hr_utility.set_location(l_proc,510);
2936: --
2937: l_update_count := l_update_count + 1;
2938: end if;
2939: end if;
2943: --
2944: if l_update_count > 1
2945: then
2946: --
2947: hr_utility.set_location(l_proc,520);
2948: --
2949: p_mutiple_fields_updated := true;
2950: else
2951: --
2948: --
2949: p_mutiple_fields_updated := true;
2950: else
2951: --
2952: hr_utility.set_location(l_proc,530);
2953: --
2954: p_mutiple_fields_updated := false;
2955: end if;
2956: hr_utility.set_location(' Leaving:'||l_proc,540);
2952: hr_utility.set_location(l_proc,530);
2953: --
2954: p_mutiple_fields_updated := false;
2955: end if;
2956: hr_utility.set_location(' Leaving:'||l_proc,540);
2957: --
2958: exception
2959: when others then
2960: hr_utility.set_location(' Leaving:'||l_proc,550);
2956: hr_utility.set_location(' Leaving:'||l_proc,540);
2957: --
2958: exception
2959: when others then
2960: hr_utility.set_location(' Leaving:'||l_proc,550);
2961: raise;
2962: End chk_multiple_fields_updated;
2963: --
2964: --
2999: l_proc varchar2(72) := g_package||'chk_offer_extended_method';
3000: l_api_updating boolean;
3001: --
3002: begin
3003: hr_utility.set_location('Entering:'|| l_proc, 10);
3004: --
3005: if hr_multi_message.no_exclusive_error(
3006: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3007: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3033: -- Check if offer_extended_method is not null.
3034: --
3035: if p_offer_extended_method is not null then
3036: --
3037: hr_utility.set_location(l_proc, 20);
3038: --
3039: -- Checks that the value for offer_extended_method is
3040: -- valid and exists on irc_offer_extended_method lookup
3041: -- within the specified date range
3053: end if;
3054: end if;
3055: end if; -- no_exclusive_error
3056: --
3057: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3058: exception
3059: when app_exception.application_exception then
3060: if hr_multi_message.exception_add
3061: (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3059: when app_exception.application_exception then
3060: if hr_multi_message.exception_add
3061: (p_associated_column1 => 'IRC_OFFERS.OFFER_EXTENDED_METHOD'
3062: ) then
3063: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3064: raise;
3065: end if;
3066: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3067: end chk_offer_extended_method;
3062: ) then
3063: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3064: raise;
3065: end if;
3066: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3067: end chk_offer_extended_method;
3068: --
3069: -- ---------------------------------------------------------------------------
3070: -- |---------------------< chk_offer_postal_service >------------------------|
3103: l_proc varchar2(72) := g_package||'chk_offer_postal_service';
3104: l_api_updating boolean;
3105: --
3106: begin
3107: hr_utility.set_location('Entering:'|| l_proc, 10);
3108: --
3109: if hr_multi_message.no_exclusive_error(
3110: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3111: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3137: -- Check if offer_postal_service is not null.
3138: --
3139: if p_offer_postal_service is not null then
3140: --
3141: hr_utility.set_location(l_proc, 20);
3142: --
3143: -- Checks that the value for offer_postal_service is
3144: -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3145: -- within the specified date range
3157: end if;
3158: end if;
3159: end if; -- no_exclusive_error
3160: --
3161: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3162: exception
3163: when app_exception.application_exception then
3164: if hr_multi_message.exception_add
3165: (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3163: when app_exception.application_exception then
3164: if hr_multi_message.exception_add
3165: (p_associated_column1 => 'IRC_OFFERS.OFFER_POSTAL_SERVICE'
3166: ) then
3167: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3168: raise;
3169: end if;
3170: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3171: end chk_offer_postal_service;
3166: ) then
3167: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3168: raise;
3169: end if;
3170: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3171: end chk_offer_postal_service;
3172: --
3173: -- ---------------------------------------------------------------------------
3174: -- |--------------------------< chk_offer_letter >---------------------------|
3208: from irc_offers
3209: where offer_id = p_offer_id;
3210: --
3211: begin
3212: hr_utility.set_location('Entering:'|| l_proc, 10);
3213: --
3214: if hr_multi_message.no_exclusive_error(
3215: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3216: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3221: and nvl(irc_iof_shd.g_old_rec.offer_status, hr_api.g_varchar2) <>
3222: nvl(p_offer_status, hr_api.g_varchar2)
3223: then
3224: --
3225: hr_utility.set_location(l_proc, 20);
3226: --
3227: open csr_offer_letter;
3228: fetch csr_offer_letter into l_offer_letter;
3229: if csr_offer_letter%notfound
3228: fetch csr_offer_letter into l_offer_letter;
3229: if csr_offer_letter%notfound
3230: then
3231: --
3232: hr_utility.set_location(l_proc, 30);
3233: --
3234: fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
3235: fnd_message.raise_error;
3236: --
3241: --
3242: if dbms_lob.getlength(l_offer_letter) = 0
3243: then
3244: --
3245: hr_utility.set_location(l_proc, 40);
3246: --
3247: fnd_message.set_name('PER','IRC_412312_UPLOAD_OFFER_LETTER');
3248: hr_multi_message.add
3249: (p_message_type => hr_multi_message.g_warning_msg
3253: --
3254: end if;
3255: end if; -- no_exclusive_error
3256: --
3257: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3258: exception
3259: when app_exception.application_exception then
3260: if hr_multi_message.exception_add
3261: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3259: when app_exception.application_exception then
3260: if hr_multi_message.exception_add
3261: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER'
3262: ) then
3263: hr_utility.set_location(' Leaving:'|| l_proc, 60);
3264: raise;
3265: end if;
3266: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3267: end chk_offer_letter;
3262: ) then
3263: hr_utility.set_location(' Leaving:'|| l_proc, 60);
3264: raise;
3265: end if;
3266: hr_utility.set_location(' Leaving:'|| l_proc, 70);
3267: end chk_offer_letter;
3268: --
3269: --
3270: -- ---------------------------------------------------------------------------
3304: l_proc varchar2(72) := g_package||'chk_offer_letter_file_type';
3305: l_api_updating boolean;
3306: --
3307: begin
3308: hr_utility.set_location('Entering:'|| l_proc, 10);
3309: --
3310: if hr_multi_message.no_exclusive_error(
3311: p_check_column1 => 'IRC_OFFERS.APPLICANT_ASSIGNMENT_ID'
3312: ,p_check_column2 => 'IRC_OFFERS.OFFER_ASSIGNMENT_ID'
3338: -- Check if offer_postal_service is not null.
3339: --
3340: if p_offer_letter_file_type is not null then
3341: --
3342: hr_utility.set_location(l_proc, 20);
3343: --
3344: -- Checks that the value for offer_postal_service is
3345: -- valid and exists on IRC_OFFER_POSTAL_SERVICE lookup
3346: -- within the specified date range
3357: end if;
3358: end if;
3359: end if; -- no_exclusive_error
3360: --
3361: hr_utility.set_location(' Leaving:'|| l_proc, 30);
3362: exception
3363: when app_exception.application_exception then
3364: if hr_multi_message.exception_add
3365: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3363: when app_exception.application_exception then
3364: if hr_multi_message.exception_add
3365: (p_associated_column1 => 'IRC_OFFERS.OFFER_LETTER_FILE_TYPE'
3366: ) then
3367: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3368: raise;
3369: end if;
3370: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3371: end chk_offer_letter_file_type;
3366: ) then
3367: hr_utility.set_location(' Leaving:'|| l_proc, 40);
3368: raise;
3369: end if;
3370: hr_utility.set_location(' Leaving:'|| l_proc, 50);
3371: end chk_offer_letter_file_type;
3372: --
3373: -- ----------------------------------------------------------------------------
3374: -- |---------------------------< insert_validate >----------------------------|
3381: --
3382: l_proc varchar2(72) := g_package||'insert_validate';
3383: --
3384: Begin
3385: hr_utility.set_location('Entering:'||l_proc, 10);
3386: --
3387: -- Call all supporting business operations
3388: --
3389: hr_utility.set_location(l_proc, 20);
3385: hr_utility.set_location('Entering:'||l_proc, 10);
3386: --
3387: -- Call all supporting business operations
3388: --
3389: hr_utility.set_location(l_proc, 20);
3390: --
3391: chk_applicant_assignment_id
3392: (p_effective_date => p_effective_date
3393: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3392: (p_effective_date => p_effective_date
3393: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3394: );
3395: --
3396: hr_utility.set_location(l_proc, 30);
3397: --
3398: chk_offer_assignment_id
3399: (p_offer_assignment_id => p_rec.offer_assignment_id
3400: );
3398: chk_offer_assignment_id
3399: (p_offer_assignment_id => p_rec.offer_assignment_id
3400: );
3401: --
3402: hr_utility.set_location(l_proc, 40);
3403: --
3404: set_vacancy_id
3405: (p_vacancy_id => p_rec.vacancy_id
3406: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3406: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3407: ,p_effective_date => p_effective_date
3408: );
3409: --
3410: hr_utility.set_location(l_proc, 50);
3411: --
3412: chk_offers_exceeds_openings
3413: (p_vacancy_id => p_rec.vacancy_id
3414: ,p_offer_status => p_rec.offer_status
3414: ,p_offer_status => p_rec.offer_status
3415: ,p_offer_id => p_rec.offer_id
3416: );
3417: --
3418: hr_utility.set_location(l_proc, 60);
3419: --
3420: chk_respondent_id
3421: (p_respondent_id => p_rec.respondent_id
3422: ,p_offer_id => p_rec.offer_id
3422: ,p_offer_id => p_rec.offer_id
3423: ,p_object_version_number => p_rec.object_version_number
3424: );
3425: --
3426: hr_utility.set_location(l_proc, 70);
3427: --
3428: set_address_id
3429: (p_address_id => p_rec.address_id
3430: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3430: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3431: ,p_effective_date => p_effective_date
3432: );
3433: --
3434: hr_utility.set_location(l_proc, 75);
3435: --
3436: chk_address_id
3437: (p_address_id => p_rec.address_id
3438: ,p_offer_id => p_rec.offer_id
3438: ,p_offer_id => p_rec.offer_id
3439: ,p_object_version_number => p_rec.object_version_number
3440: );
3441: --
3442: hr_utility.set_location(l_proc, 80);
3443: --
3444: chk_template_id
3445: (p_template_id => p_rec.template_id
3446: ,p_offer_id => p_rec.offer_id
3447: ,p_object_version_number => p_rec.object_version_number
3448: ,p_effective_date => p_effective_date
3449: );
3450: --
3451: hr_utility.set_location(l_proc, 90);
3452: --
3453: gen_offer_version
3454: (p_offer_version => p_rec.offer_version
3455: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3454: (p_offer_version => p_rec.offer_version
3455: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3456: );
3457: --
3458: hr_utility.set_location(l_proc, 100);
3459: --
3460: chk_latest_offer
3461: (p_latest_offer => p_rec.latest_offer
3462: ,p_offer_id => p_rec.offer_id
3465: ,p_effective_date => p_effective_date
3466: ,p_object_version_number => p_rec.object_version_number
3467: );
3468: --
3469: hr_utility.set_location(l_proc, 110);
3470: --
3471: chk_offer_version_combination
3472: (p_offer_id => p_rec.offer_id
3473: ,p_offer_version => p_rec.offer_version
3474: ,p_applicant_assignment_id => p_rec.applicant_assignment_id
3475: ,p_object_version_number => p_rec.object_version_number
3476: );
3477: --
3478: hr_utility.set_location(l_proc, 120);
3479: --
3480: chk_offer_status
3481: (p_offer_id => p_rec.offer_id
3482: ,p_offer_status => p_rec.offer_status
3483: ,p_effective_date => p_effective_date
3484: ,p_object_version_number => p_rec.object_version_number
3485: );
3486: --
3487: hr_utility.set_location(l_proc, 130);
3488: --
3489: chk_offer_extended_method
3490: (p_offer_id => p_rec.offer_id
3491: ,p_offer_extended_method => p_rec.offer_extended_method
3492: ,p_effective_date => p_effective_date
3493: ,p_object_version_number => p_rec.object_version_number
3494: );
3495: --
3496: hr_utility.set_location(l_proc, 140);
3497: --
3498: chk_offer_postal_service
3499: (p_offer_id => p_rec.offer_id
3500: ,p_offer_postal_service => p_rec.offer_postal_service
3501: ,p_effective_date => p_effective_date
3502: ,p_object_version_number => p_rec.object_version_number
3503: );
3504: --
3505: hr_utility.set_location(l_proc, 150);
3506: --
3507: chk_offer_letter_file_type
3508: (p_offer_id => p_rec.offer_id
3509: ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3510: ,p_effective_date => p_effective_date
3511: ,p_object_version_number => p_rec.object_version_number
3512: );
3513: --
3514: hr_utility.set_location(l_proc, 170);
3515: --
3516: irc_iof_bus.chk_df(p_rec);
3517: --
3518: hr_utility.set_location(l_proc, 180);
3514: hr_utility.set_location(l_proc, 170);
3515: --
3516: irc_iof_bus.chk_df(p_rec);
3517: --
3518: hr_utility.set_location(l_proc, 180);
3519: --
3520: hr_utility.set_location(' Leaving:'||l_proc, 180);
3521: End insert_validate;
3522: --
3516: irc_iof_bus.chk_df(p_rec);
3517: --
3518: hr_utility.set_location(l_proc, 180);
3519: --
3520: hr_utility.set_location(' Leaving:'||l_proc, 180);
3521: End insert_validate;
3522: --
3523: -- ----------------------------------------------------------------------------
3524: -- |---------------------------< update_validate >----------------------------|
3530: --
3531: l_proc varchar2(72) := g_package||'update_validate';
3532: --
3533: Begin
3534: hr_utility.set_location('Entering:'||l_proc, 10);
3535: --
3536: -- Call all supporting business operations
3537: --
3538: hr_utility.set_location(l_proc, 20);
3534: hr_utility.set_location('Entering:'||l_proc, 10);
3535: --
3536: -- Call all supporting business operations
3537: --
3538: hr_utility.set_location(l_proc, 20);
3539: --
3540: chk_non_updateable_args
3541: (p_effective_date => p_effective_date
3542: ,p_rec => p_rec
3541: (p_effective_date => p_effective_date
3542: ,p_rec => p_rec
3543: );
3544: --
3545: hr_utility.set_location(l_proc, 30);
3546: --
3547: chk_offers_exceeds_openings
3548: (p_vacancy_id => p_rec.vacancy_id
3549: ,p_offer_status => p_rec.offer_status
3549: ,p_offer_status => p_rec.offer_status
3550: ,p_offer_id => p_rec.offer_id
3551: );
3552: --
3553: hr_utility.set_location(l_proc, 35);
3554: --
3555: chk_respondent_id
3556: (p_respondent_id => p_rec.respondent_id
3557: ,p_offer_id => p_rec.offer_id
3557: ,p_offer_id => p_rec.offer_id
3558: ,p_object_version_number => p_rec.object_version_number
3559: );
3560: --
3561: hr_utility.set_location(l_proc, 40);
3562: --
3563: chk_expiry_date
3564: (p_expiry_date => p_rec.expiry_date
3565: ,p_offer_status => p_rec.offer_status
3569: ,p_offer_shipping_date => p_rec.offer_shipping_date
3570: ,p_effective_date => p_effective_date
3571: );
3572: --
3573: hr_utility.set_location(l_proc, 45);
3574: --
3575: chk_address_id
3576: (p_address_id => p_rec.address_id
3577: ,p_offer_id => p_rec.offer_id
3577: ,p_offer_id => p_rec.offer_id
3578: ,p_object_version_number => p_rec.object_version_number
3579: );
3580: --
3581: hr_utility.set_location(l_proc, 50);
3582: --
3583: chk_template_id
3584: (p_template_id => p_rec.template_id
3585: ,p_offer_id => p_rec.offer_id
3586: ,p_object_version_number => p_rec.object_version_number
3587: ,p_effective_date => p_effective_date
3588: );
3589: --
3590: hr_utility.set_location(l_proc, 60);
3591: --
3592: chk_latest_offer
3593: (p_latest_offer => p_rec.latest_offer
3594: ,p_offer_id => p_rec.offer_id
3597: ,p_effective_date => p_effective_date
3598: ,p_object_version_number => p_rec.object_version_number
3599: );
3600: --
3601: hr_utility.set_location(l_proc, 70);
3602: --
3603: chk_offer_status
3604: (p_offer_id => p_rec.offer_id
3605: ,p_offer_status => p_rec.offer_status
3606: ,p_effective_date => p_effective_date
3607: ,p_object_version_number => p_rec.object_version_number
3608: );
3609: --
3610: hr_utility.set_location(l_proc, 80);
3611: --
3612: chk_offer_status_update
3613: (p_current_offer_record => p_rec
3614: );
3612: chk_offer_status_update
3613: (p_current_offer_record => p_rec
3614: );
3615: --
3616: hr_utility.set_location(l_proc, 90);
3617: --
3618: chk_offer_extended_method
3619: (p_offer_id => p_rec.offer_id
3620: ,p_offer_extended_method => p_rec.offer_extended_method
3621: ,p_effective_date => p_effective_date
3622: ,p_object_version_number => p_rec.object_version_number
3623: );
3624: --
3625: hr_utility.set_location(l_proc, 100);
3626: --
3627: chk_offer_postal_service
3628: (p_offer_id => p_rec.offer_id
3629: ,p_offer_postal_service => p_rec.offer_postal_service
3630: ,p_effective_date => p_effective_date
3631: ,p_object_version_number => p_rec.object_version_number
3632: );
3633: --
3634: hr_utility.set_location(l_proc, 110);
3635: --
3636: chk_offer_letter
3637: (p_offer_id => p_rec.offer_id
3638: ,p_offer_status => p_rec.offer_status
3637: (p_offer_id => p_rec.offer_id
3638: ,p_offer_status => p_rec.offer_status
3639: );
3640: --
3641: hr_utility.set_location(l_proc, 120);
3642: --
3643: chk_offer_letter_file_type
3644: (p_offer_id => p_rec.offer_id
3645: ,p_offer_letter_file_type => p_rec.offer_letter_file_type
3646: ,p_effective_date => p_effective_date
3647: ,p_object_version_number => p_rec.object_version_number
3648: );
3649: --
3650: hr_utility.set_location(l_proc, 130);
3651: --
3652: irc_iof_bus.chk_df(p_rec);
3653: --
3654: hr_utility.set_location(' Leaving:'||l_proc, 140);
3650: hr_utility.set_location(l_proc, 130);
3651: --
3652: irc_iof_bus.chk_df(p_rec);
3653: --
3654: hr_utility.set_location(' Leaving:'||l_proc, 140);
3655: End update_validate;
3656: --
3657: -- ----------------------------------------------------------------------------
3658: -- |---------------------------< delete_validate >----------------------------|
3663: --
3664: l_proc varchar2(72) := g_package||'delete_validate';
3665: --
3666: Begin
3667: hr_utility.set_location('Entering:'||l_proc, 5);
3668: --
3669: -- Call all supporting business operations
3670: --
3671: hr_utility.set_location(' Leaving:'||l_proc, 10);
3667: hr_utility.set_location('Entering:'||l_proc, 5);
3668: --
3669: -- Call all supporting business operations
3670: --
3671: hr_utility.set_location(' Leaving:'||l_proc, 10);
3672: End delete_validate;
3673: --
3674: end irc_iof_bus;