25: --
26: cursor csr_sec_grp is
27: select pbg.security_group_id
28: from per_business_groups_perf pbg
29: , per_gen_hierarchy_versions pgv
30: where pgv.hierarchy_version_id = p_hierarchy_version_id
31: and pbg.business_group_id (+) = pgv.business_group_id;
32: --
33: -- Declare local variables
85: --
86: cursor csr_leg_code is
87: select pbg.legislation_code
88: from per_business_groups_perf pbg
89: , per_gen_hierarchy_versions pgv
90: where pgv.hierarchy_version_id = p_hierarchy_version_id
91: and pbg.business_group_id (+) = pgv.business_group_id;
92: --
93: -- Declare local variables
254: then
255: --
256: hr_dflex_utility.ins_or_upd_descflex_attribs
257: (p_appl_short_name => 'PER'
258: ,p_descflex_name => 'PER_GEN_HIERARCHY_VERSIONS_DDF'
259: ,p_attribute_category => p_rec.information_category
260: ,p_attribute1_name => 'INFORMATION1'
261: ,p_attribute1_value => p_rec.information1
262: ,p_attribute2_name => 'INFORMATION2'
434: -- b) During insert.
435: --
436: hr_dflex_utility.ins_or_upd_descflex_attribs
437: (p_appl_short_name => 'PER'
438: ,p_descflex_name => 'PER_GEN_HIERARCHY_VERSIONS'
439: ,p_attribute_category => p_rec.ATTRIBUTE_CATEGORY
440: ,p_attribute1_name => 'ATTRIBUTE1'
441: ,p_attribute1_value => p_rec.attribute1
442: ,p_attribute2_name => 'ATTRIBUTE2'
616: --
617: -- {End Of Comments}
618: --
619: PROCEDURE chk_hierarchy_id
620: ( p_hierarchy_id IN per_gen_hierarchy.hierarchy_id%TYPE,
621: p_business_group_id IN per_gen_hierarchy.business_group_id%TYPE)
622: IS
623: l_proc VARCHAR2(72) := g_package||'chk_hierarchy_id';
624: l_exists VARCHAR2(1) := 'N';
617: -- {End Of Comments}
618: --
619: PROCEDURE chk_hierarchy_id
620: ( p_hierarchy_id IN per_gen_hierarchy.hierarchy_id%TYPE,
621: p_business_group_id IN per_gen_hierarchy.business_group_id%TYPE)
622: IS
623: l_proc VARCHAR2(72) := g_package||'chk_hierarchy_id';
624: l_exists VARCHAR2(1) := 'N';
625: --
624: l_exists VARCHAR2(1) := 'N';
625: --
626: cursor csr_hierarchy_id IS
627: SELECT 'Y'
628: FROM per_gen_hierarchy
629: WHERE ( (p_business_group_id is not null and business_group_id = p_business_group_id)
630: or p_business_group_id is null )
631: AND hierarchy_id = p_hierarchy_id;
632: --
706: SELECT nvl(Information2, 'Y')
707: FROM per_shared_types
708: WHERE lookup_type = 'HIERARCHY_TYPE'
709: AND system_type_cd = (SELECT Type
710: FROM Per_Gen_Hierarchy
711: WHERE hierarchy_id = p_hierarchy_id)
712: AND system_type_cd = shared_type_code;
713: --
714: CURSOR csr_version_exists IS
712: AND system_type_cd = shared_type_code;
713: --
714: CURSOR csr_version_exists IS
715: SELECT 'Y'
716: FROM Per_Gen_Hierarchy_Versions
717: WHERE Hierarchy_Id = p_hierarchy_id;
718: --
719: --
720: l_multiple_versions Varchar2(1) := 'Y';
775: --
776: -- {End Of Comments}
777: --
778: PROCEDURE chk_version_number
779: ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
780: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
781: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
782: )
783: IS
776: -- {End Of Comments}
777: --
778: PROCEDURE chk_version_number
779: ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
780: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
781: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
782: )
783: IS
784: l_proc VARCHAR2(72) := g_package||'chk_version_number';
777: --
778: PROCEDURE chk_version_number
779: ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
780: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
781: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
782: )
783: IS
784: l_proc VARCHAR2(72) := g_package||'chk_version_number';
785: l_api_updating boolean;
848: --
849: -- {End Of Comments}
850: --
851: PROCEDURE chk_duplicate_version
852: ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
853: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
854: )
855: IS
856: l_proc VARCHAR2(72) := g_package||'chk_duplicate_version';
849: -- {End Of Comments}
850: --
851: PROCEDURE chk_duplicate_version
852: ( p_version_number IN per_gen_hierarchy_versions.version_number%TYPE
853: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
854: )
855: IS
856: l_proc VARCHAR2(72) := g_package||'chk_duplicate_version';
857: l_exists VARCHAR2(1);
857: l_exists VARCHAR2(1);
858:
859: CURSOR csr_dup_version IS
860: SELECT '1'
861: FROM per_gen_hierarchy_versions
862: WHERE hierarchy_id = p_hierarchy_id
863: and
864: version_number = p_version_number;
865: --
934: --
935: -- {End Of Comments}
936: --
937: PROCEDURE chk_date_from
938: (p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
939: , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
935: -- {End Of Comments}
936: --
937: PROCEDURE chk_date_from
938: (p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
939: , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
943: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
936: --
937: PROCEDURE chk_date_from
938: (p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
939: , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
943: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
944: ) IS
937: PROCEDURE chk_date_from
938: (p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
939: , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
943: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
944: ) IS
945: --
939: , p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
940: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
941: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
942: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
943: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
944: ) IS
945: --
946: l_proc VARCHAR2(72) := g_package||'chk_date_from';
947: l_date_between varchar2(1) := 'N';
957: --
958: --
959: CURSOR csr_other_open_ended_vers IS
960: SELECT 'Y'
961: FROM per_gen_hierarchy_versions
962: WHERE p_date_from > date_from
963: AND date_to is null
964: AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
965: AND hierarchy_id = p_hierarchy_id;
966: --
967: --
968: CURSOR csr_other_vers IS
969: SELECT 'Y'
970: FROM per_gen_hierarchy_versions
971: WHERE p_date_from >= date_from
972: AND (p_date_from <= date_to
973: OR date_to is null)
974: AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
1086: --
1087: -- {End Of Comments}
1088: --
1089: PROCEDURE chk_date_to
1090: (p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
1091: , p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
1092: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094: , p_version_number IN number
1087: -- {End Of Comments}
1088: --
1089: PROCEDURE chk_date_to
1090: (p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
1091: , p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
1092: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094: , p_version_number IN number
1095: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
1088: --
1089: PROCEDURE chk_date_to
1090: (p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
1091: , p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
1092: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094: , p_version_number IN number
1095: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
1096: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
1089: PROCEDURE chk_date_to
1090: (p_date_to IN per_gen_hierarchy_versions.date_to%TYPE
1091: , p_date_from IN per_gen_hierarchy_versions.date_from%TYPE
1092: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094: , p_version_number IN number
1095: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
1096: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
1097: ) IS
1092: , p_hierarchy_version_id IN per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1093: , p_hierarchy_id IN per_gen_hierarchy_versions.hierarchy_id%TYPE
1094: , p_version_number IN number
1095: , p_business_group_id IN hr_all_organization_units.business_group_id%TYPE
1096: , p_object_version_number IN per_gen_hierarchy_versions.object_version_number%TYPE
1097: ) IS
1098: --
1099: l_proc VARCHAR2(72) := g_package||'chk_date_to';
1100: l_date_between varchar2(1) := 'N';
1111: --
1112: --
1113: CURSOR csr_other_vers IS
1114: SELECT 'Y'
1115: FROM per_gen_hierarchy_versions
1116: WHERE l_date_to
1117: between date_from
1118: and nvl(date_to,l_date_to)
1119: AND hierarchy_version_id <> nvl(p_hierarchy_version_id,-1)
1185: -- |--------------------------< chk_status >----------------------------------|
1186: -- ----------------------------------------------------------------------------
1187: --
1188: Procedure chk_status
1189: (p_status in per_gen_hierarchy_versions.status%TYPE
1190: , p_effective_date in date
1191: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1192: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1193: )
1187: --
1188: Procedure chk_status
1189: (p_status in per_gen_hierarchy_versions.status%TYPE
1190: , p_effective_date in date
1191: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1192: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1193: )
1194: is
1195: --
1188: Procedure chk_status
1189: (p_status in per_gen_hierarchy_versions.status%TYPE
1190: , p_effective_date in date
1191: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1192: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1193: )
1194: is
1195: --
1196: -- Declare local variables
1244: -- |--------------------------< chk_validate_flag >---------------------------|
1245: -- ----------------------------------------------------------------------------
1246: --
1247: Procedure chk_validate_flag
1248: (p_validate_flag in per_gen_hierarchy_versions.validate_flag%TYPE
1249: , p_effective_date in date
1250: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1251: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1252: )
1246: --
1247: Procedure chk_validate_flag
1248: (p_validate_flag in per_gen_hierarchy_versions.validate_flag%TYPE
1249: , p_effective_date in date
1250: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1251: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1252: )
1253: is
1254: --
1247: Procedure chk_validate_flag
1248: (p_validate_flag in per_gen_hierarchy_versions.validate_flag%TYPE
1249: , p_effective_date in date
1250: , p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1251: , p_object_version_number in per_gen_hierarchy_versions.object_version_number%TYPE
1252: )
1253: is
1254: --
1255: -- Declare local variables
1302: -- ----------------------------------------------------------------------------
1303: -- |---------------------------< chk_delete >---------------------------------|
1304: -- ----------------------------------------------------------------------------
1305: Procedure chk_delete
1306: (p_hierarchy_version_id in per_gen_hierarchy_versions.hierarchy_version_id%TYPE
1307: ) is
1308: --
1309: -- Declare local variables
1310: --
1312: l_proc varchar2(72) := g_package||'chk_delete';
1313: --
1314: cursor csr_node_exists is
1315: select 'Y'
1316: from per_gen_hierarchy_nodes
1317: where hierarchy_version_id = p_hierarchy_version_id;
1318: --
1319: Begin
1320: --