[Home] [Help]
972: , cp_org_structure_version_id Number
973: , cp_organization_process Varchar2 )
974: is
975: select TREE.organization_id_start
976: from hr_organization_units org
977: , (select ele.organization_id_parent organization_id_start
978: from per_org_structure_elements ele
979: where cp_organization_process in ('ISNR', 'ISRO')
980: connect by prior ele.organization_id_child = ele.organization_id_parent
983: and ele.org_structure_version_id = cp_org_structure_version_id) TREE
984: where TREE.organization_id_start = org.organization_id
985: UNION
986: select TREE.organization_id_start
987: from hr_organization_units org
988: , (select ele.organization_id_child organization_id_start
989: from per_org_structure_elements ele
990: where cp_organization_process in ('ISNR', 'ISRO')
991: connect by prior ele.organization_id_child = ele.organization_id_parent
994: and ele.org_structure_version_id = cp_org_structure_version_id) TREE
995: where TREE.organization_id_start = org.organization_id
996: UNION
997: select org.organization_id organization_id_start
998: from hr_organization_units org
999: where org.organization_id = cp_organization_id
1000: order by 1;
1001: --
1002: cursor c_child
1005: , cp_organization_process Varchar2 )
1006: is
1007: select TREE.organization_id_group
1008: , TREE.organization_id_child
1009: from hr_organization_units org
1010: , (select cp_organization_id_start organization_id_group
1011: , ele.organization_id_child organization_id_child
1012: from per_org_structure_elements ele
1013: where cp_organization_process in ('SIRO', 'ISRO')
1018: where TREE.organization_id_child = org.organization_id
1019: UNION
1020: select org.organization_id organization_id_group
1021: , org.organization_id organization_id_child
1022: from hr_organization_units org
1023: where org.organization_id = cp_organization_id_start
1024: order by 1,2;
1025: --
1026: l_org_id Number;
1591: cursor c_get_loc_org is
1592: select org.location_id
1593: ,loc.country
1594: from hr_locations loc
1595: ,hr_organization_units org
1596: where org.organization_id = p_organization_id
1597: and org.location_id = loc.location_id;
1598:
1599: cursor c_get_loc_bus is
1691: open ref_csr for
1692: 'select loc.' || g_region_segment ||
1693: ' ,loc.location_id' ||
1694: ' from hr_locations loc' ||
1695: ' , hr_organization_units hou' ||
1696: ' where loc.location_id = hou.location_id' ||
1697: ' and hou.organization_id = :p_organization_id'
1698: using p_organization_id;
1699:
1784: cursor get_area_org is
1785: select nvl(ter.parent_territory_code, 'Unassigned')
1786: from bis_territory_hierarchies_v ter
1787: , hr_locations loc
1788: , hr_organization_units hou
1789: where ter.child_territory_code(+) = loc.country
1790: and decode(ter.parent_territory_type,null,'AREA'
1791: ,ter.parent_territory_type) = 'AREA'
1792: and hou.location_id = loc.location_id
1822: -- Then look at the location on the organization
1823: cursor get_country_org is
1824: select loc.country
1825: from hr_locations loc
1826: , hr_organization_units hou
1827: where loc.location_id = hou.location_id
1828: and hou.organization_id = p_organization_id;
1829:
1830: -- Then look at the location on the business group
1948:
1949: open reg_cv for
1950: 'select loc.'||g_region_segment||
1951: ' from hr_locations loc'||
1952: ' , hr_organization_units hou'||
1953: ' where loc.location_id = hou.location_id'||
1954: ' and hou.organization_id = :p_organization_id'
1955: using p_organization_id;
1956: