47: (p_position_transaction_id => p_position_transaction_id,
48: p_object_version_number => p_object_version_number);
49: --
50: if (l_api_updating
51: and nvl(p_position_transaction_id,hr_api.g_number)
52: <> pqh_ptx_shd.g_old_rec.position_transaction_id) then
53: --
54: -- raise error as PK has changed
55: --
119: (p_position_transaction_id => p_position_transaction_id,
120: p_object_version_number => p_object_version_number);
121: --
122: if (l_api_updating
123: and nvl(p_organization_id,hr_api.g_number)
124: <> nvl(pqh_ptx_shd.g_old_rec.organization_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_organization_id is not null then
127: --
120: p_object_version_number => p_object_version_number);
121: --
122: if (l_api_updating
123: and nvl(p_organization_id,hr_api.g_number)
124: <> nvl(pqh_ptx_shd.g_old_rec.organization_id,hr_api.g_number)
125: or not l_api_updating) and
126: p_organization_id is not null then
127: --
128: -- check if organization_id value exists in hr_all_organization_units table
195: (p_position_transaction_id => p_position_transaction_id,
196: p_object_version_number => p_object_version_number);
197: --
198: if (l_api_updating
199: and nvl(p_position_definition_id,hr_api.g_number)
200: <> nvl(pqh_ptx_shd.g_old_rec.position_definition_id,hr_api.g_number)
201: or not l_api_updating) and
202: p_position_definition_id is not null then
203: --
196: p_object_version_number => p_object_version_number);
197: --
198: if (l_api_updating
199: and nvl(p_position_definition_id,hr_api.g_number)
200: <> nvl(pqh_ptx_shd.g_old_rec.position_definition_id,hr_api.g_number)
201: or not l_api_updating) and
202: p_position_definition_id is not null then
203: --
204: -- check if position_definition_id value exists in per_position_definitions table
271: (p_position_transaction_id => p_position_transaction_id,
272: p_object_version_number => p_object_version_number);
273: --
274: if (l_api_updating
275: and nvl(p_job_id,hr_api.g_number)
276: <> nvl(pqh_ptx_shd.g_old_rec.job_id,hr_api.g_number)
277: or not l_api_updating) and
278: p_job_id is not null then
279: --
272: p_object_version_number => p_object_version_number);
273: --
274: if (l_api_updating
275: and nvl(p_job_id,hr_api.g_number)
276: <> nvl(pqh_ptx_shd.g_old_rec.job_id,hr_api.g_number)
277: or not l_api_updating) and
278: p_job_id is not null then
279: --
280: -- check if job_id value exists in per_jobs table
347: (p_position_transaction_id => p_position_transaction_id,
348: p_object_version_number => p_object_version_number);
349: --
350: if (l_api_updating
351: and nvl(p_location_id,hr_api.g_number)
352: <> nvl(pqh_ptx_shd.g_old_rec.location_id,hr_api.g_number)
353: or not l_api_updating) and
354: p_location_id is not null then
355: --
348: p_object_version_number => p_object_version_number);
349: --
350: if (l_api_updating
351: and nvl(p_location_id,hr_api.g_number)
352: <> nvl(pqh_ptx_shd.g_old_rec.location_id,hr_api.g_number)
353: or not l_api_updating) and
354: p_location_id is not null then
355: --
356: -- check if location_id value exists in hr_locations_all table
423: (p_position_transaction_id => p_position_transaction_id,
424: p_object_version_number => p_object_version_number);
425: --
426: if (l_api_updating
427: and nvl(p_availability_status_id,hr_api.g_number)
428: <> nvl(pqh_ptx_shd.g_old_rec.availability_status_id,hr_api.g_number)
429: or not l_api_updating) and
430: p_availability_status_id is not null then
431: --
424: p_object_version_number => p_object_version_number);
425: --
426: if (l_api_updating
427: and nvl(p_availability_status_id,hr_api.g_number)
428: <> nvl(pqh_ptx_shd.g_old_rec.availability_status_id,hr_api.g_number)
429: or not l_api_updating) and
430: p_availability_status_id is not null then
431: --
432: -- check if availability_status_id value exists in per_shared_types table
499: (p_position_transaction_id => p_position_transaction_id,
500: p_object_version_number => p_object_version_number);
501: --
502: if (l_api_updating
503: and nvl(p_entry_grade_id,hr_api.g_number)
504: <> nvl(pqh_ptx_shd.g_old_rec.entry_grade_id,hr_api.g_number)
505: or not l_api_updating) and
506: p_entry_grade_id is not null then
507: --
500: p_object_version_number => p_object_version_number);
501: --
502: if (l_api_updating
503: and nvl(p_entry_grade_id,hr_api.g_number)
504: <> nvl(pqh_ptx_shd.g_old_rec.entry_grade_id,hr_api.g_number)
505: or not l_api_updating) and
506: p_entry_grade_id is not null then
507: --
508: -- check if entry_grade_id value exists in per_grades table
572: p_object_version_number => p_object_version_number);
573: --
574: if (l_api_updating
575: and p_work_term_end_month_cd
576: <> nvl(pqh_ptx_shd.g_old_rec.work_term_end_month_cd,hr_api.g_varchar2)
577: or not l_api_updating)
578: and p_work_term_end_month_cd is not null then
579: --
580: -- check if value of lookup falls within lookup type.
578: and p_work_term_end_month_cd is not null then
579: --
580: -- check if value of lookup falls within lookup type.
581: --
582: if hr_api.not_exists_in_hr_lookups
583: (p_lookup_type => 'MONTH_CODE',
584: p_lookup_code => p_work_term_end_month_cd,
585: p_effective_date => p_effective_date) then
586: --
640: p_object_version_number => p_object_version_number);
641: --
642: if (l_api_updating
643: and p_work_term_end_day_cd
644: <> nvl(pqh_ptx_shd.g_old_rec.work_term_end_day_cd,hr_api.g_varchar2)
645: or not l_api_updating)
646: and p_work_term_end_day_cd is not null then
647: --
648: -- check if value of lookup falls within lookup type.
646: and p_work_term_end_day_cd is not null then
647: --
648: -- check if value of lookup falls within lookup type.
649: --
650: if hr_api.not_exists_in_hr_lookups
651: (p_lookup_type => 'DAY_CODE',
652: p_lookup_code => p_work_term_end_day_cd,
653: p_effective_date => p_effective_date) then
654: --
708: p_object_version_number => p_object_version_number);
709: --
710: if (l_api_updating
711: and p_work_period_type_cd
712: <> nvl(pqh_ptx_shd.g_old_rec.work_period_type_cd,hr_api.g_varchar2)
713: or not l_api_updating)
714: and p_work_period_type_cd is not null then
715: --
716: -- check if value of lookup falls within lookup type.
714: and p_work_period_type_cd is not null then
715: --
716: -- check if value of lookup falls within lookup type.
717: --
718: if hr_api.not_exists_in_hr_lookups
719: (p_lookup_type => 'YES_NO',
720: p_lookup_code => p_work_period_type_cd,
721: p_effective_date => p_effective_date) then
722: --
776: p_object_version_number => p_object_version_number);
777: --
778: if (l_api_updating
779: and p_works_council_approval_flag
780: <> nvl(pqh_ptx_shd.g_old_rec.works_council_approval_flag,hr_api.g_varchar2)
781: or not l_api_updating)
782: and p_works_council_approval_flag is not null then
783: --
784: -- check if value of lookup falls within lookup type.
782: and p_works_council_approval_flag is not null then
783: --
784: -- check if value of lookup falls within lookup type.
785: --
786: if hr_api.not_exists_in_hr_lookups
787: (p_lookup_type => 'YES_NO',
788: p_lookup_code => p_works_council_approval_flag,
789: p_effective_date => p_effective_date) then
790: --
844: p_object_version_number => p_object_version_number);
845: --
846: if (l_api_updating
847: and p_term_start_month_cd
848: <> nvl(pqh_ptx_shd.g_old_rec.term_start_month_cd,hr_api.g_varchar2)
849: or not l_api_updating)
850: and p_term_start_month_cd is not null then
851: --
852: -- check if value of lookup falls within lookup type.
850: and p_term_start_month_cd is not null then
851: --
852: -- check if value of lookup falls within lookup type.
853: --
854: if hr_api.not_exists_in_hr_lookups
855: (p_lookup_type => 'MONTH_CODE',
856: p_lookup_code => p_term_start_month_cd,
857: p_effective_date => p_effective_date) then
858: --
912: p_object_version_number => p_object_version_number);
913: --
914: if (l_api_updating
915: and p_term_start_day_cd
916: <> nvl(pqh_ptx_shd.g_old_rec.term_start_day_cd,hr_api.g_varchar2)
917: or not l_api_updating)
918: and p_term_start_day_cd is not null then
919: --
920: -- check if value of lookup falls within lookup type.
918: and p_term_start_day_cd is not null then
919: --
920: -- check if value of lookup falls within lookup type.
921: --
922: if hr_api.not_exists_in_hr_lookups
923: (p_lookup_type => 'DAY_CODE',
924: p_lookup_code => p_term_start_day_cd,
925: p_effective_date => p_effective_date) then
926: --
980: p_object_version_number => p_object_version_number);
981: --
982: if (l_api_updating
983: and p_seasonal_flag
984: <> nvl(pqh_ptx_shd.g_old_rec.seasonal_flag,hr_api.g_varchar2)
985: or not l_api_updating)
986: and p_seasonal_flag is not null then
987: --
988: -- check if value of lookup falls within lookup type.
986: and p_seasonal_flag is not null then
987: --
988: -- check if value of lookup falls within lookup type.
989: --
990: if hr_api.not_exists_in_hr_lookups
991: (p_lookup_type => 'YES_NO',
992: p_lookup_code => p_seasonal_flag,
993: p_effective_date => p_effective_date) then
994: --
1056: p_object_version_number => p_object_version_number);
1057: --
1058: if (l_api_updating
1059: and p_review_flag
1060: <> nvl(pqh_ptx_shd.g_old_rec.review_flag,hr_api.g_varchar2)
1061: or not l_api_updating)
1062: and p_review_flag is not null then
1063: --
1064: -- check if value of lookup falls within lookup type.
1062: and p_review_flag is not null then
1063: --
1064: -- check if value of lookup falls within lookup type.
1065: --
1066: if hr_api.not_exists_in_hr_lookups
1067: (p_lookup_type => 'YES_NO',
1068: p_lookup_code => p_review_flag,
1069: p_effective_date => p_effective_date) then
1070: --
1135: p_object_version_number => p_object_version_number);
1136: --
1137: if (l_api_updating
1138: and p_replacement_required_flag
1139: <> nvl(pqh_ptx_shd.g_old_rec.replacement_required_flag,hr_api.g_varchar2)
1140: or not l_api_updating)
1141: and p_replacement_required_flag is not null then
1142: --
1143: -- check if value of lookup falls within lookup type.
1141: and p_replacement_required_flag is not null then
1142: --
1143: -- check if value of lookup falls within lookup type.
1144: --
1145: if hr_api.not_exists_in_hr_lookups
1146: (p_lookup_type => 'YES_NO',
1147: p_lookup_code => p_replacement_required_flag,
1148: p_effective_date => p_effective_date) then
1149: --
1203: p_object_version_number => p_object_version_number);
1204: --
1205: if (l_api_updating
1206: and p_probation_period_unit_cd
1207: <> nvl(pqh_ptx_shd.g_old_rec.probation_period_unit_cd,hr_api.g_varchar2)
1208: or not l_api_updating)
1209: and p_probation_period_unit_cd is not null then
1210: --
1211: -- check if value of lookup falls within lookup type.
1209: and p_probation_period_unit_cd is not null then
1210: --
1211: -- check if value of lookup falls within lookup type.
1212: --
1213: if hr_api.not_exists_in_hr_lookups
1214: (p_lookup_type => 'FREQUENCY',
1215: p_lookup_code => p_probation_period_unit_cd,
1216: p_effective_date => p_effective_date) then
1217: --
1271: p_object_version_number => p_object_version_number);
1272: --
1273: if (l_api_updating
1274: and p_permit_recruitment_flag
1275: <> nvl(pqh_ptx_shd.g_old_rec.permit_recruitment_flag,hr_api.g_varchar2)
1276: or not l_api_updating)
1277: and p_permit_recruitment_flag is not null then
1278: --
1279: -- check if value of lookup falls within lookup type.
1277: and p_permit_recruitment_flag is not null then
1278: --
1279: -- check if value of lookup falls within lookup type.
1280: --
1281: if hr_api.not_exists_in_hr_lookups
1282: (p_lookup_type => 'YES_NO',
1283: p_lookup_code => p_permit_recruitment_flag,
1284: p_effective_date => p_effective_date) then
1285: --
1339: p_object_version_number => p_object_version_number);
1340: --
1341: if (l_api_updating
1342: and p_permanent_temporary_flag
1343: <> nvl(pqh_ptx_shd.g_old_rec.permanent_temporary_flag,hr_api.g_varchar2)
1344: or not l_api_updating)
1345: and p_permanent_temporary_flag is not null then
1346: --
1347: -- check if value of lookup falls within lookup type.
1345: and p_permanent_temporary_flag is not null then
1346: --
1347: -- check if value of lookup falls within lookup type.
1348: --
1349: if hr_api.not_exists_in_hr_lookups
1350: (p_lookup_type => 'YES_NO',
1351: p_lookup_code => p_permanent_temporary_flag,
1352: p_effective_date => p_effective_date) then
1353: --
1407: p_object_version_number => p_object_version_number);
1408: --
1409: if (l_api_updating
1410: and p_pay_term_end_month_cd
1411: <> nvl(pqh_ptx_shd.g_old_rec.pay_term_end_month_cd,hr_api.g_varchar2)
1412: or not l_api_updating)
1413: and p_pay_term_end_month_cd is not null then
1414: --
1415: -- check if value of lookup falls within lookup type.
1413: and p_pay_term_end_month_cd is not null then
1414: --
1415: -- check if value of lookup falls within lookup type.
1416: --
1417: if hr_api.not_exists_in_hr_lookups
1418: (p_lookup_type => 'MONTH_CODE',
1419: p_lookup_code => p_pay_term_end_month_cd,
1420: p_effective_date => p_effective_date) then
1421: --
1475: p_object_version_number => p_object_version_number);
1476: --
1477: if (l_api_updating
1478: and p_pay_term_end_day_cd
1479: <> nvl(pqh_ptx_shd.g_old_rec.pay_term_end_day_cd,hr_api.g_varchar2)
1480: or not l_api_updating)
1481: and p_pay_term_end_day_cd is not null then
1482: --
1483: -- check if value of lookup falls within lookup type.
1481: and p_pay_term_end_day_cd is not null then
1482: --
1483: -- check if value of lookup falls within lookup type.
1484: --
1485: if hr_api.not_exists_in_hr_lookups
1486: (p_lookup_type => 'DAY_CODE',
1487: p_lookup_code => p_pay_term_end_day_cd,
1488: p_effective_date => p_effective_date) then
1489: --
1543: p_object_version_number => p_object_version_number);
1544: --
1545: if (l_api_updating
1546: and p_overlap_unit_cd
1547: <> nvl(pqh_ptx_shd.g_old_rec.overlap_unit_cd,hr_api.g_varchar2)
1548: or not l_api_updating)
1549: and p_overlap_unit_cd is not null then
1550: --
1551: -- check if value of lookup falls within lookup type.
1549: and p_overlap_unit_cd is not null then
1550: --
1551: -- check if value of lookup falls within lookup type.
1552: --
1553: if hr_api.not_exists_in_hr_lookups
1554: (p_lookup_type => 'FREQUENCY',
1555: p_lookup_code => p_overlap_unit_cd,
1556: p_effective_date => p_effective_date) then
1557: --
1611: p_object_version_number => p_object_version_number);
1612: --
1613: if (l_api_updating
1614: and p_bargaining_unit_cd
1615: <> nvl(pqh_ptx_shd.g_old_rec.bargaining_unit_cd,hr_api.g_varchar2)
1616: or not l_api_updating)
1617: and p_bargaining_unit_cd is not null then
1618: --
1619: -- check if value of lookup falls within lookup type.
1617: and p_bargaining_unit_cd is not null then
1618: --
1619: -- check if value of lookup falls within lookup type.
1620: --
1621: if hr_api.not_exists_in_hr_lookups
1622: (p_lookup_type => 'BARGAINING_UNIT_CODE',
1623: p_lookup_code => p_bargaining_unit_cd,
1624: p_effective_date => p_effective_date) then
1625: --
1750: (p_position_transaction_id => p_position_transaction_id
1751: ,p_object_version_number => p_object_version_number);
1752:
1753: if (((l_api_updating and p_work_period_type_cd
1754: <> nvl(pqh_ptx_shd.g_old_rec.work_period_type_cd,hr_api.g_varchar2)) or not l_api_updating)
1755: and nvl(p_WORK_PERIOD_TYPE_CD,'N') = 'Y') then
1756: hr_utility.set_location('Check permit_extended_pay:'||l_proc,10);
1757: l_permit_extended_pay := permit_extended_pay(p_position_transaction_id => p_position_transaction_id);
1758: hr_utility.set_location('Checking permit_extended_pay complete:'||l_proc,10);
1797: (p_position_transaction_id => p_position_transaction_id,
1798: p_object_version_number => p_object_version_number);
1799: --
1800: if (l_api_updating
1801: and nvl(date_effective,hr_api.g_date)
1802: <> nvl(pqh_ptx_shd.g_old_rec.date_effective,hr_api.g_date)
1803: or not l_api_updating) and
1804: date_effective is not null then
1805: --
1798: p_object_version_number => p_object_version_number);
1799: --
1800: if (l_api_updating
1801: and nvl(date_effective,hr_api.g_date)
1802: <> nvl(pqh_ptx_shd.g_old_rec.date_effective,hr_api.g_date)
1803: or not l_api_updating) and
1804: date_effective is not null then
1805: --
1806: if current_org_prop_end_date < date_effective then
1837: end if;
1838: end if;
1839: --
1840: if (l_api_updating
1841: and nvl(current_org_prop_end_date,hr_api.g_date)
1842: <> nvl(pqh_ptx_shd.g_old_rec.current_org_prop_end_date,hr_api.g_date)
1843: or not l_api_updating) and
1844: current_org_prop_end_date is not null then
1845: --
1838: end if;
1839: --
1840: if (l_api_updating
1841: and nvl(current_org_prop_end_date,hr_api.g_date)
1842: <> nvl(pqh_ptx_shd.g_old_rec.current_org_prop_end_date,hr_api.g_date)
1843: or not l_api_updating) and
1844: current_org_prop_end_date is not null then
1845: --
1846: if current_org_prop_end_date < date_effective then
1849: end if;
1850: end if;
1851: --
1852: if (l_api_updating
1853: and nvl(current_job_prop_end_date,hr_api.g_date)
1854: <> nvl(pqh_ptx_shd.g_old_rec.current_job_prop_end_date,hr_api.g_date)
1855: or not l_api_updating) and
1856: current_job_prop_end_date is not null then
1857: --
1850: end if;
1851: --
1852: if (l_api_updating
1853: and nvl(current_job_prop_end_date,hr_api.g_date)
1854: <> nvl(pqh_ptx_shd.g_old_rec.current_job_prop_end_date,hr_api.g_date)
1855: or not l_api_updating) and
1856: current_job_prop_end_date is not null then
1857: --
1858: if current_job_prop_end_date < date_effective then
1861: end if;
1862: end if;
1863: --
1864: if (l_api_updating
1865: and nvl(avail_status_prop_end_date,hr_api.g_date)
1866: <> nvl(pqh_ptx_shd.g_old_rec.avail_status_prop_end_date,hr_api.g_date)
1867: or not l_api_updating) and
1868: avail_status_prop_end_date is not null then
1869: --
1862: end if;
1863: --
1864: if (l_api_updating
1865: and nvl(avail_status_prop_end_date,hr_api.g_date)
1866: <> nvl(pqh_ptx_shd.g_old_rec.avail_status_prop_end_date,hr_api.g_date)
1867: or not l_api_updating) and
1868: avail_status_prop_end_date is not null then
1869: --
1870: l_avail_status_start_date := hr_general.DECODE_AVAIL_STATUS_START_DATE (
1877: end if;
1878: end if;
1879: --
1880: if (l_api_updating
1881: and nvl(earliest_hire_date,hr_api.g_date)
1882: <> nvl(pqh_ptx_shd.g_old_rec.earliest_hire_date,hr_api.g_date)
1883: or not l_api_updating) and
1884: earliest_hire_date is not null then
1885: --
1878: end if;
1879: --
1880: if (l_api_updating
1881: and nvl(earliest_hire_date,hr_api.g_date)
1882: <> nvl(pqh_ptx_shd.g_old_rec.earliest_hire_date,hr_api.g_date)
1883: or not l_api_updating) and
1884: earliest_hire_date is not null then
1885: --
1886: if earliest_hire_date < date_effective then
1895: end if;
1896: end if;
1897: --
1898: if (l_api_updating
1899: and nvl(fill_by_date,hr_api.g_date)
1900: <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1901: or not l_api_updating) and
1902: fill_by_date is not null then
1903: --
1896: end if;
1897: --
1898: if (l_api_updating
1899: and nvl(fill_by_date,hr_api.g_date)
1900: <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1901: or not l_api_updating) and
1902: fill_by_date is not null then
1903: --
1904: if fill_by_date < nvl(earliest_hire_date, date_effective) then
1908: end if;
1909: end if;
1910: --
1911: if (l_api_updating
1912: and nvl(fill_by_date,hr_api.g_date)
1913: <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1914: or not l_api_updating) and
1915: fill_by_date is not null then
1916: --
1909: end if;
1910: --
1911: if (l_api_updating
1912: and nvl(fill_by_date,hr_api.g_date)
1913: <> nvl(pqh_ptx_shd.g_old_rec.fill_by_date,hr_api.g_date)
1914: or not l_api_updating) and
1915: fill_by_date is not null then
1916: --
1917: if proposed_date_for_layoff <= date_effective then
1943: ,p_object_version_number => p_object_version_number);
1944:
1945: if (l_api_updating
1946: and p_seasonal_flag
1947: <> nvl(pqh_ptx_shd.g_old_rec.seasonal_flag,hr_api.g_varchar2)
1948: and (p_seasonal_flag='N' or p_seasonal_flag is null)) then
1949: open c_seasonal;
1950: fetch c_seasonal into l_dummy;
1951: if c_seasonal%found then
1979: ,p_object_version_number => p_object_version_number);
1980:
1981: if (l_api_updating
1982: and nvl(p_overlap_period,-1)
1983: <> nvl(pqh_ptx_shd.g_old_rec.overlap_period,hr_api.g_number)
1984: and p_overlap_period is null) then
1985: hr_utility.set_location('Checking for Overlap Dates in Position Extra Info:'||l_proc,20);
1986: open c_overlap;
1987: fetch c_overlap into l_dummy;
2176: ,p_work_period_type_cd => p_rec.work_period_type_cd
2177: ,p_object_version_number => p_rec.object_version_number
2178: );
2179: */
2180: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2181: --
2182: hr_utility.set_location(' Leaving:'||l_proc, 10);
2183: End insert_validate;
2184: --
2365: ,p_overlap_period => p_rec.overlap_period
2366: ,p_object_version_number => p_rec.object_version_number);
2367: --
2368: */
2369: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2370: --
2371: hr_utility.set_location(' Leaving:'||l_proc, 10);
2372: End update_validate;
2373: --
2414: hr_utility.set_location('Entering:'|| l_proc, 10);
2415: --
2416: -- Ensure that all the mandatory parameter are not null
2417: --
2418: hr_api.mandatory_arg_error(p_api_name => l_proc,
2419: p_argument => 'position_transaction_id',
2420: p_argument_value => p_position_transaction_id);
2421: --
2422: open csr_leg_code;