[Home] [Help]
148: is
149: select ose.organization_id_parent
150: from per_organization_structures ost
151: , per_org_structure_versions osv
152: , per_org_structure_elements ose
153: where ost.organization_structure_id = cp_ost_id
154: and ost.organization_structure_id = osv.organization_structure_id
155: and osv.org_structure_version_id = ose.org_structure_version_id
156: and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
155: and osv.org_structure_version_id = ose.org_structure_version_id
156: and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
157: and not exists
158: ( select null
159: from per_org_structure_elements ose2
160: where ose2.org_structure_version_id = osv.org_structure_version_id
161: and ose.organization_id_parent = ose2.organization_id_child );
162:
163: cursor c_prm
250: is
251: select TREE.organization_id_start
252: from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
253: , (select ele.organization_id_parent organization_id_start
254: from per_org_structure_elements ele
255: where ele.org_structure_version_id = cp_org_structure_version_id
256: and cp_organization_process in ('ISNR', 'ISRO')
257: connect by prior ele.organization_id_child = ele.organization_id_parent
258: and ele.org_structure_version_id = cp_org_structure_version_id
262: UNION
263: select TREE.organization_id_start
264: from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
265: , (select ele.organization_id_child organization_id_start
266: from per_org_structure_elements ele
267: where ele.org_structure_version_id = cp_org_structure_version_id
268: and cp_organization_process in ('ISNR', 'ISRO')
269: connect by prior ele.organization_id_child = ele.organization_id_parent
270: and ele.org_structure_version_id = cp_org_structure_version_id
315: , TREE.organization_id_child
316: from hr_all_organization_units org -- cbridge 05-DEC-2000 115.6
317: , (select cp_organization_id_start organization_id_group
318: , ele.organization_id_child organization_id_child
319: from per_org_structure_elements ele
320: where ele.org_structure_version_id = cp_org_structure_version_id
321: and cp_organization_process in ('SIRO', 'ISRO')
322: connect by prior ele.organization_id_child = ele.organization_id_parent
323: -- JTitmas Bug# 1296567 Altered line below to keep query in same hierarchy
497: , ose.organization_id_parent top_org_id
498: , ose.org_structure_version_id ver_id
499: from per_organization_structures ost
500: , per_org_structure_versions osv
501: , per_org_structure_elements ose
502: where ost.organization_structure_id = osv.organization_structure_id
503: and osv.org_structure_version_id = ose.org_structure_version_id
504: and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
505: and not exists
503: and osv.org_structure_version_id = ose.org_structure_version_id
504: and trunc(sysdate) between nvl(osv.date_from,trunc(sysdate)) and nvl(osv.date_to,sysdate)
505: and not exists
506: ( select null
507: from per_org_structure_elements ose2
508: where ose2.org_structure_version_id = osv.org_structure_version_id
509: and ose.organization_id_parent = ose2.organization_id_child );
510: --
511: -- This cursor has org_structure_version_id passed into cp_ost_id and a
517: ( cp_ost_id Number
518: , cp_org_id Number )
519: is
520: select organization_id_child organization_id
521: from per_org_structure_elements
522: where org_structure_version_id = cp_ost_id
523: UNION
524: select organization_id
525: from hr_all_organization_units -- S.Bhattal, 18-AUG-99, 115.2 -- cbridge 05-DEC-2000 115.6