68: (p_rule_set_id => p_rule_set_id,
69: p_object_version_number => p_object_version_number);
70: --
71: if (l_api_updating
72: and nvl(p_rule_set_id,hr_api.g_number)
73: <> pqh_rst_shd.g_old_rec.rule_set_id) then
74: --
75: -- raise error as PK has changed
76: --
140: (p_rule_set_id => p_rule_set_id,
141: p_object_version_number => p_object_version_number);
142: --
143: if (l_api_updating
144: and nvl(p_rule_set_name,hr_api.g_varchar2)
145: <> pqh_rst_shd.g_old_rec.rule_set_name
146: OR NOT l_api_updating) then
147: --
148: -- raise error as PK has changed
211: (p_rule_set_id => p_rule_set_id,
212: p_object_version_number => p_object_version_number);
213: --
214: if (l_api_updating and
215: (nvl(p_referenced_rule_set_id,hr_api.g_number)
216: <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number)
217: or nvl(p_rule_category,hr_api.g_number)
218: <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_number))
219: or not l_api_updating) then
212: p_object_version_number => p_object_version_number);
213: --
214: if (l_api_updating and
215: (nvl(p_referenced_rule_set_id,hr_api.g_number)
216: <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number)
217: or nvl(p_rule_category,hr_api.g_number)
218: <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_number))
219: or not l_api_updating) then
220: --
213: --
214: if (l_api_updating and
215: (nvl(p_referenced_rule_set_id,hr_api.g_number)
216: <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number)
217: or nvl(p_rule_category,hr_api.g_number)
218: <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_number))
219: or not l_api_updating) then
220: --
221: if p_referenced_rule_set_id is not null then
214: if (l_api_updating and
215: (nvl(p_referenced_rule_set_id,hr_api.g_number)
216: <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number)
217: or nvl(p_rule_category,hr_api.g_number)
218: <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_number))
219: or not l_api_updating) then
220: --
221: if p_referenced_rule_set_id is not null then
222: open c1;
299: (p_rule_set_id => p_rule_set_id,
300: p_object_version_number => p_object_version_number);
301: --
302: if (l_api_updating
303: and nvl(p_business_group_id,hr_api.g_number)
304: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
305: or not l_api_updating) then
306: --
307: -- check if business_group_id exists in hr_all_organization_units table
300: p_object_version_number => p_object_version_number);
301: --
302: if (l_api_updating
303: and nvl(p_business_group_id,hr_api.g_number)
304: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
305: or not l_api_updating) then
306: --
307: -- check if business_group_id exists in hr_all_organization_units table
308: --
395: (p_rule_set_id => p_rule_set_id,
396: p_object_version_number => p_object_version_number);
397: --
398: if (l_api_updating and (
399: nvl(p_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
396: p_object_version_number => p_object_version_number);
397: --
398: if (l_api_updating and (
399: nvl(p_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
404: or nvl(p_referenced_rule_set_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number))
397: --
398: if (l_api_updating and (
399: nvl(p_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
404: or nvl(p_referenced_rule_set_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number))
405: or not l_api_updating) then
398: if (l_api_updating and (
399: nvl(p_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
404: or nvl(p_referenced_rule_set_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number))
405: or not l_api_updating) then
406: --
399: nvl(p_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
404: or nvl(p_referenced_rule_set_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number))
405: or not l_api_updating) then
406: --
407: -- check for duplicate rule sets
400: or nvl(p_organization_structure_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
401: or nvl(p_business_group_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
402: or nvl(p_starting_organization_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
403: or nvl(p_rule_category,hr_api.g_varchar2) <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
404: or nvl(p_referenced_rule_set_id,hr_api.g_number) <> nvl(pqh_rst_shd.g_old_rec.referenced_rule_set_id,hr_api.g_number))
405: or not l_api_updating) then
406: --
407: -- check for duplicate rule sets
408: --
551: -- if business group is changed or organization is changed this rule should kick in
552: -- provided organization id is there
553: --
554: if (l_api_updating
555: and (nvl(p_business_group_id,hr_api.g_number)
556: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
557: or nvl(p_organization_id,hr_api.g_number)
558: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number))
559: or not l_api_updating)
552: -- provided organization id is there
553: --
554: if (l_api_updating
555: and (nvl(p_business_group_id,hr_api.g_number)
556: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
557: or nvl(p_organization_id,hr_api.g_number)
558: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number))
559: or not l_api_updating)
560: and p_organization_id is not null then
553: --
554: if (l_api_updating
555: and (nvl(p_business_group_id,hr_api.g_number)
556: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
557: or nvl(p_organization_id,hr_api.g_number)
558: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number))
559: or not l_api_updating)
560: and p_organization_id is not null then
561: --
554: if (l_api_updating
555: and (nvl(p_business_group_id,hr_api.g_number)
556: <> nvl(pqh_rst_shd.g_old_rec.business_group_id,hr_api.g_number)
557: or nvl(p_organization_id,hr_api.g_number)
558: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number))
559: or not l_api_updating)
560: and p_organization_id is not null then
561: --
562: -- check if organization value exists in hr_all_organization_units table
644: (p_rule_set_id => p_rule_set_id,
645: p_object_version_number => p_object_version_number);
646: --
647: if (l_api_updating
648: and (nvl(p_organization_structure_id,hr_api.g_number)
649: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
650: or nvl(p_organization_structure_id,hr_api.g_number)
651: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
652: or not l_api_updating) and
645: p_object_version_number => p_object_version_number);
646: --
647: if (l_api_updating
648: and (nvl(p_organization_structure_id,hr_api.g_number)
649: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
650: or nvl(p_organization_structure_id,hr_api.g_number)
651: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
652: or not l_api_updating) and
653: p_organization_structure_id is not null then
646: --
647: if (l_api_updating
648: and (nvl(p_organization_structure_id,hr_api.g_number)
649: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
650: or nvl(p_organization_structure_id,hr_api.g_number)
651: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
652: or not l_api_updating) and
653: p_organization_structure_id is not null then
654: --
647: if (l_api_updating
648: and (nvl(p_organization_structure_id,hr_api.g_number)
649: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number)
650: or nvl(p_organization_structure_id,hr_api.g_number)
651: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
652: or not l_api_updating) and
653: p_organization_structure_id is not null then
654: --
655: -- check if organization_structure_id value exists in per_organization_structures table
729: p_object_version_number => p_object_version_number);
730: --
731: if (l_api_updating
732: and p_rule_level_cd
733: <> nvl(pqh_rst_shd.g_old_rec.rule_level_cd,hr_api.g_varchar2)
734: or not l_api_updating)
735: and p_rule_level_cd is not null then
736: --
737: -- check if value of lookup falls within lookup type.
735: and p_rule_level_cd is not null then
736: --
737: -- check if value of lookup falls within lookup type.
738: --
739: if hr_api.not_exists_in_hr_lookups
740: (p_lookup_type => 'PQH_RULE_LEVEL',
741: p_lookup_code => p_rule_level_cd,
742: p_effective_date => p_effective_date) then
743: --
799: (p_rule_set_id => p_rule_set_id,
800: p_object_version_number => p_object_version_number);
801: --
802: if (l_api_updating
803: and (p_rule_applicability <> nvl(pqh_rst_shd.g_old_rec.rule_applicability,hr_api.g_varchar2)
804: or p_rule_category <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2))
805: or not l_api_updating)
806: and p_rule_applicability is not null then
807: --
800: p_object_version_number => p_object_version_number);
801: --
802: if (l_api_updating
803: and (p_rule_applicability <> nvl(pqh_rst_shd.g_old_rec.rule_applicability,hr_api.g_varchar2)
804: or p_rule_category <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2))
805: or not l_api_updating)
806: and p_rule_applicability is not null then
807: --
808: -- check if value of lookup falls within lookup type.
806: and p_rule_applicability is not null then
807: --
808: -- check if value of lookup falls within lookup type.
809: --
810: if hr_api.not_exists_in_hr_lookups
811: (p_lookup_type => 'PQH_CBR_APPLICABILITY',
812: p_lookup_code => p_rule_applicability,
813: p_effective_date => p_effective_date) then
814: --
886: p_object_version_number => p_object_version_number);
887: --
888: if (l_api_updating
889: and p_rule_category
890: <> nvl(pqh_rst_shd.g_old_rec.rule_category,hr_api.g_varchar2)
891: or not l_api_updating)
892: and p_rule_category is not null then
893: --
894: -- check if value of lookup falls within lookup type.
892: and p_rule_category is not null then
893: --
894: -- check if value of lookup falls within lookup type.
895: --
896: if hr_api.not_exists_in_hr_lookups
897: (p_lookup_type => 'PQH_CBR_RULE_CATEGORY',
898: p_lookup_code => p_rule_category,
899: p_effective_date => p_effective_date) then
900: --
974: (p_rule_set_id => p_rule_set_id,
975: p_object_version_number => p_object_version_number);
976: --
977: if (l_api_updating
978: and ( nvl(p_starting_organization_id,hr_api.g_number)
979: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
980: or nvl(p_organization_structure_id,hr_api.g_number)
981: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
982: or not l_api_updating) then
975: p_object_version_number => p_object_version_number);
976: --
977: if (l_api_updating
978: and ( nvl(p_starting_organization_id,hr_api.g_number)
979: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
980: or nvl(p_organization_structure_id,hr_api.g_number)
981: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
982: or not l_api_updating) then
983: --
976: --
977: if (l_api_updating
978: and ( nvl(p_starting_organization_id,hr_api.g_number)
979: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
980: or nvl(p_organization_structure_id,hr_api.g_number)
981: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
982: or not l_api_updating) then
983: --
984: If p_organization_structure_id IS NOT NULL then
977: if (l_api_updating
978: and ( nvl(p_starting_organization_id,hr_api.g_number)
979: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
980: or nvl(p_organization_structure_id,hr_api.g_number)
981: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
982: or not l_api_updating) then
983: --
984: If p_organization_structure_id IS NOT NULL then
985: --
1027: (p_rule_set_id => p_rule_set_id,
1028: p_object_version_number => p_object_version_number);
1029: --
1030: if (l_api_updating
1031: and ( nvl(p_starting_organization_id,hr_api.g_number)
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1028: p_object_version_number => p_object_version_number);
1029: --
1030: if (l_api_updating
1031: and ( nvl(p_starting_organization_id,hr_api.g_number)
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1036: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
1029: --
1030: if (l_api_updating
1031: and ( nvl(p_starting_organization_id,hr_api.g_number)
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1036: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
1037: or not l_api_updating) then
1030: if (l_api_updating
1031: and ( nvl(p_starting_organization_id,hr_api.g_number)
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1036: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
1037: or not l_api_updating) then
1038: --
1031: and ( nvl(p_starting_organization_id,hr_api.g_number)
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1036: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
1037: or not l_api_updating) then
1038: --
1039: if p_organization_structure_id is not null and p_organization_id is not null then
1032: <> nvl(pqh_rst_shd.g_old_rec.starting_organization_id,hr_api.g_number)
1033: or nvl(p_organization_id,hr_api.g_number)
1034: <> nvl(pqh_rst_shd.g_old_rec.organization_id,hr_api.g_number)
1035: or nvl(p_organization_structure_id,hr_api.g_number)
1036: <> nvl(pqh_rst_shd.g_old_rec.organization_structure_id,hr_api.g_number))
1037: or not l_api_updating) then
1038: --
1039: if p_organization_structure_id is not null and p_organization_id is not null then
1040: hr_utility.set_location('org hier entered, org must be null',20);
1176: p_rule_category => p_rec.rule_category,
1177: p_effective_date => p_effective_date,
1178: p_object_version_number => p_rec.object_version_number);
1179: --
1180: -- call hr_api.validate_bus_grp_id
1181: --
1182: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1183: --
1184: hr_utility.set_location(' Leaving:'||l_proc, 10);
1178: p_object_version_number => p_rec.object_version_number);
1179: --
1180: -- call hr_api.validate_bus_grp_id
1181: --
1182: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1183: --
1184: hr_utility.set_location(' Leaving:'||l_proc, 10);
1185: End insert_validate;
1186: --
1282: p_rule_category => p_rec.rule_category,
1283: p_effective_date => p_effective_date,
1284: p_object_version_number => p_rec.object_version_number);
1285: --
1286: -- call hr_api.validate_bus_grp_id
1287: --
1288: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1289: --
1290: hr_utility.set_location(' Leaving:'||l_proc, 10);
1284: p_object_version_number => p_rec.object_version_number);
1285: --
1286: -- call hr_api.validate_bus_grp_id
1287: --
1288: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1289: --
1290: hr_utility.set_location(' Leaving:'||l_proc, 10);
1291: End update_validate;
1292: --
1333: hr_utility.set_location('Entering:'|| l_proc, 10);
1334: --
1335: -- Ensure that all the mandatory parameter are not null
1336: --
1337: hr_api.mandatory_arg_error(p_api_name => l_proc,
1338: p_argument => 'rule_set_id',
1339: p_argument_value => p_rule_set_id);
1340: --
1341: open csr_leg_code;