25: --
26: cursor csr_sec_grp is
27: select pbg.security_group_id
28: from per_business_groups pbg
29: , per_org_structure_elements ose
30: where ose.org_structure_element_id = p_org_structure_element_id
31: and pbg.business_group_id = ose.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 pbg
89: , per_org_structure_elements ose
90: where ose.org_structure_element_id = p_org_structure_element_id
91: and pbg.business_group_id (+) = ose.business_group_id;
92: --
93: -- Declare local variables
218:
219: Removing following check on oraganization hierarchy so that updation of
220: organization nodes can be done in it.We don't need to put extra check
221: for ensuring not having cycle into organization hierarcy because this
222: task is achieved by a constraint PER_ORG_STRUCTURE_ELEMENTS_UK2 according
223: to which ORG_STRUCTURE_VERSION_ID and ORGANIZATION_ID_CHILD is the unique
224: combination in table PER_ORG_STRUCTURE_ELEMENTS.
225:
226: if nvl(p_rec.organization_id_parent, hr_api.g_number) <>
220: organization nodes can be done in it.We don't need to put extra check
221: for ensuring not having cycle into organization hierarcy because this
222: task is achieved by a constraint PER_ORG_STRUCTURE_ELEMENTS_UK2 according
223: to which ORG_STRUCTURE_VERSION_ID and ORGANIZATION_ID_CHILD is the unique
224: combination in table PER_ORG_STRUCTURE_ELEMENTS.
225:
226: if nvl(p_rec.organization_id_parent, hr_api.g_number) <>
227: nvl(per_ose_shd.g_old_rec.organization_id_parent
228: ,hr_api.g_number
254: -- |---------------------------< chk_children_exist >----------------------------|
255: -- -------------------------------------------------------------------------------
256: PROCEDURE chk_children_exist
257: (p_org_structure_version_id in
258: per_org_structure_elements.org_structure_version_id%TYPE
259: ,p_organization_id_child in
260: per_org_structure_elements.organization_id_child%TYPE
261: ) is
262: --
256: PROCEDURE chk_children_exist
257: (p_org_structure_version_id in
258: per_org_structure_elements.org_structure_version_id%TYPE
259: ,p_organization_id_child in
260: per_org_structure_elements.organization_id_child%TYPE
261: ) is
262: --
263: l_temp VARCHAR2(1);
264: --
270: select null
271: into l_temp
272: from sys.dual
273: where exists (select null
274: from per_org_structure_elements ose
275: where ose.org_structure_version_id =
276: p_org_structure_version_id
277: and ose.organization_id_parent =
278: p_organization_id_child);
291: -- |---------------------------< chk_top_org >----------------------------|
292: -- ------------------------------------------------------------------------
293: PROCEDURE chk_top_org
294: (p_org_structure_version_id
295: in per_org_structure_elements.org_structure_version_id%TYPE
296: ,p_org_structure_element_id
297: in per_org_structure_elements.org_structure_element_id%TYPE
298: ,p_organization_id_child
299: in per_org_structure_elements.organization_id_child%TYPE
293: PROCEDURE chk_top_org
294: (p_org_structure_version_id
295: in per_org_structure_elements.org_structure_version_id%TYPE
296: ,p_org_structure_element_id
297: in per_org_structure_elements.org_structure_element_id%TYPE
298: ,p_organization_id_child
299: in per_org_structure_elements.organization_id_child%TYPE
300: ,p_organization_id_parent
301: in per_org_structure_elements.organization_id_parent%TYPE
295: in per_org_structure_elements.org_structure_version_id%TYPE
296: ,p_org_structure_element_id
297: in per_org_structure_elements.org_structure_element_id%TYPE
298: ,p_organization_id_child
299: in per_org_structure_elements.organization_id_child%TYPE
300: ,p_organization_id_parent
301: in per_org_structure_elements.organization_id_parent%TYPE
302: ) is
303: --
297: in per_org_structure_elements.org_structure_element_id%TYPE
298: ,p_organization_id_child
299: in per_org_structure_elements.organization_id_child%TYPE
300: ,p_organization_id_parent
301: in per_org_structure_elements.organization_id_parent%TYPE
302: ) is
303: --
304: l_temp VARCHAR2(1);
305: --
328: and ((psp.organization_id = p_organization_id_child)
329: or(psp.organization_id = p_organization_id_parent
330: and not exists (
331: select null
332: from per_org_structure_elements ose
333: where ose.org_structure_version_id =
334: p_org_structure_version_id
335: and ose.organization_id_child =
336: p_organization_id_parent
336: p_organization_id_parent
337: )
338: and not exists (
339: select null
340: from per_org_structure_elements ose
341: where ose.org_structure_version_id =
342: p_org_structure_version_id
343: and ose.org_structure_element_id <>
344: p_org_structure_element_id
361: -- |---------------------------< chk_pa_org >----------------------------|
362: -- ------------------------------------------------------------------------
363: PROCEDURE chk_pa_org
364: (p_org_structure_element_id
365: in per_org_structure_elements.org_structure_element_id%TYPE
366: ) is
367: begin
368: --
369: -- Run the validation PROCEDURE writtrn by PA development group.
552: and nvl(org.DATE_TO,hr_general.end_of_time);
553:
554: CURSOR csr_ele IS
555: SELECT 'X'
556: from per_org_structure_elements ele
557: where ele.org_structure_version_id = p_org_structure_version_id
558: and ele.organization_id_child = p_organization_id_child;
559:
560: --- bug fix 3820767 starts here
559:
560: --- bug fix 3820767 starts here
561: CURSOR csr_chk_circular IS
562: SELECT organization_id_parent
563: from per_org_structure_elements
564: where org_structure_version_id=p_org_structure_version_id
565: START WITH organization_id_child=p_organization_id_parent
566: and org_structure_version_id=p_org_structure_version_id
567: CONNECT BY organization_id_child= prior organization_id_parent