Oracle EBS Organizational Hierarchy
DECLARE
l_effective_date DATE := TO_DATE ('01-JAN-1951', 'DD-MON-YYYY');
l_org_structure_version_id NUMBER := NULL;
l_business_group_id NUMBER := NULL;
l_inactive_org_warning BOOLEAN;
l_org_structure_element_id NUMBER := NULL;
l_object_version_number NUMBER := NULL;
l_org_child_id NUMBER := NULL;
l_org_parent_id NUMBER := NULL;
BEGIN
--
Get Structure Version ID
BEGIN
SELECT posv.org_structure_version_id, pos.business_group_id
INTO l_org_structure_version_id, l_business_group_id
FROM per_organization_structures pos, per_org_structure_versions posv
WHERE UPPER (pos.name) = 'XXXX ORGANIZATION HIERARCHY'
AND pos.primary_structure_flag = 'Y'
AND posv.organization_structure_id =
pos.organization_structure_id
AND posv.version_number = 1
AND pos.business_group_id =
FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID');
EXCEPTION
WHEN OTHERS
THEN
l_org_structure_version_id := NULL;
l_business_group_id := NULL;
END;
--
Get Organization Parent
BEGIN
SELECT organization_id
INTO l_org_parent_id
FROM hr_all_organization_units
WHERE UPPER (name) = 'SECURITY
AND ROYAL GUARD'
AND SYSDATE BETWEEN date_from
AND NVL (date_to,
TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'));
EXCEPTION
WHEN OTHERS
THEN
l_org_parent_id := NULL;
END;
--
Get Organization Child
BEGIN
SELECT organization_id
INTO l_org_child_id
FROM hr_all_organization_units
WHERE UPPER (name) =
'1021106
-LEBANON PALACE - BAHAR SAFI-SECURITY AND ROYAL GUARD'
AND SYSDATE BETWEEN date_from
AND NVL (date_to,
TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'));
EXCEPTION
WHEN OTHERS
THEN
l_org_child_id := NULL;
END;
IF l_org_structure_version_id IS NOT NULL
AND l_org_child_id IS NOT NULL
AND l_org_parent_id IS NOT NULL
THEN
BEGIN
HR_HIERARCHY_ELEMENT_API.
create_hierarchy_element (
p_validate => FALSE,
p_effective_date => l_effective_date,
p_organization_id_parent => l_org_parent_id,
p_org_structure_version_id => l_org_structure_version_id,
p_organization_id_child => l_org_child_id,
p_business_group_id => l_business_group_id,
p_pos_control_enabled_flag => 'N',
p_inactive_org_warning => l_inactive_org_warning,
p_org_structure_element_id => l_org_structure_element_id,
p_object_version_number => l_object_version_number);
COMMIT;
DBMS_OUTPUT.
put_line (
l_org_structure_element_id
|| ' has
been created successfully !!! ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Inner
Block Exception: ' || SQLERRM);
END;
ELSE
DBMS_OUTPUT.put_line ('ID''s
not found !!!!');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main
Block Exception: ' || SQLERRM);
END;
