[Home] [Help]
221: loc.postal_code zip_code,
222: upper(replace(replace(hoi2.org_information2,'-',''),',','')) naics, --removed hyphens and commas bug 13905482
223: upper(replace(replace(hoi2.org_information4,'-',''),',','')) duns, --removed hyphens and commas bug 13905482
224: upper(replace(replace(hoi2.org_information3,'-',''),',','')) ein --removed hyphens and commas bug 13905482
225: from per_gen_hierarchy_nodes pgn,
226: hr_all_organization_units hou,
227: hr_organization_information hoi1,
228: hr_organization_information hoi2,
229: hr_locations_all loc
248: hr_organization_information hoi1
249: ,hr_locations_all cloc
250: ,hr_organization_units hou
251: ,hr_organization_information hoi2
252: ,per_gen_hierarchy_nodes pgn
253: where
254: hoi1.organization_id = pgn.entity_id
255: and hoi1.org_information_context = 'VETS_Spec'
256: and hoi1.organization_id = hou.organization_id
1176: upper(replace(replace(hlei2.lei_information6,'-',''),',','')) ein, --removed hyphens and commas bug 13905482
1177: hlei2.lei_information10 hq,
1178: eloc.location_id,
1179: pghn.hierarchy_node_id
1180: from per_gen_hierarchy_nodes pghn,
1181: hr_location_extra_info hlei1,
1182: hr_location_extra_info hlei2,
1183: hr_locations_all eloc
1184: where pghn.hierarchy_version_id = p_hierarchy_version_id
1249: and hoi2.org_information_context = 'Reporting Categories'
1250: and p_end_date between ass.effective_start_date and ass.effective_end_date
1251: and ass.location_id in
1252: (select entity_id
1253: from per_gen_hierarchy_nodes
1254: where hierarchy_version_id = p_hierarchy_version_id
1255: and (hierarchy_node_id = l_hierarchy_node_id
1256: or parent_hierarchy_node_id = l_hierarchy_node_id
1257: ))
1314: and hoi2.org_information_context = 'Reporting Categories'
1315: and p_end_date between ass.effective_start_date and ass.effective_end_date
1316: and ass.location_id in
1317: (select entity_id
1318: from per_gen_hierarchy_nodes
1319: where hierarchy_version_id = p_hierarchy_version_id
1320: and (hierarchy_node_id = l_hierarchy_node_id
1321: or parent_hierarchy_node_id = l_hierarchy_node_id
1322: ))
1380: AND hoi2.org_information_context = 'Reporting Categories'
1381: AND hoi1.organization_id = hoi2.organization_id)
1382: and ass.location_id in
1383: (select entity_id
1384: from per_gen_hierarchy_nodes
1385: where hierarchy_version_id = p_hierarchy_version_id
1386: and (hierarchy_node_id = l_hierarchy_node_id
1387: or parent_hierarchy_node_id = l_hierarchy_node_id
1388: ))
1438: AND hoi2.org_information_context = 'Reporting Categories'
1439: AND hoi1.organization_id = hoi2.organization_id)
1440: and ass.location_id in
1441: (select entity_id
1442: from per_gen_hierarchy_nodes
1443: where hierarchy_version_id = p_hierarchy_version_id
1444: and (hierarchy_node_id = l_hierarchy_node_id
1445: or parent_hierarchy_node_id = l_hierarchy_node_id
1446: ))
1475: )
1476: AND paf.business_group_id = p_business_group_id
1477: AND paf.location_id in /*bug 14803681 - Removed to_char*/
1478: (SELECT entity_id
1479: FROM per_gen_hierarchy_nodes
1480: WHERE hierarchy_version_id = p_hierarchy_version_id
1481: AND (hierarchy_node_id = l_hierarchy_node_id
1482: OR parent_hierarchy_node_id = l_hierarchy_node_id
1483: ))
1518: and paf2.effective_start_date
1519: <= p_end_date)
1520: AND paf.location_id in /* bug:14803681 - Removed to_char*/
1521: (SELECT entity_id
1522: FROM per_gen_hierarchy_nodes
1523: WHERE hierarchy_version_id = p_hierarchy_version_id
1524: AND (hierarchy_node_id = l_hierarchy_node_id
1525: OR parent_hierarchy_node_id = l_hierarchy_node_id
1526: ))
2199: l_string varchar2(2000);
2200: --
2201: cursor c2 is
2202: select count(*)
2203: from per_gen_hierarchy_nodes
2204: where node_type = 'EST'
2205: and hierarchy_version_id = p_hierarchy_version_id;
2206: --
2207: l_count number;