1 PACKAGE BODY per_jp_report_common_pkg
2 -- $Header: pejpcmrp.pkb 120.0.12010000.7 2009/07/20 16:50:06 mdarbha noship $
3 -- *************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development.
5 -- * All rights reserved
6 -- *************************************************************************
7 -- *
8 -- * PROGRAM NAME
9 -- * pejpcmrp.pkb
10 -- *
11 -- * DESCRIPTION
12 -- * This script creates the package body of per_jp_report_common_pkg
13 -- *
14 -- * DEPENDENCIES
15 -- * None
16 -- *
17 -- * CALLED BY
18 -- * Concurrent Program
19 -- *
20 -- * LAST UPDATE DATE 08-JUN-2009
21 -- * Date the program has been modified for the last time
22 -- *
23 -- * HISTORY
24 -- * =======
25 -- *
26 -- * DATE AUTHOR(S) VERSION BUG NO DESCRIPTION
27 -- * -----------+---------+-----------------+----------+------------------------------------------------------------------------------------------------------------------------------------------
28 -- * 26-MAY-2009 SPATTEM 120.0.12010000.1 8558615 Creation
29 -- * 08-JUN-2009 SPATTEM 120.0.12010000.2 8558615 Changes done as per review Comments
30 -- * 23-JUN-2009 SPATTEM 120.0.12010000.5 8623767 Changed the lcu_org_hierarchy query to get data from all levels.
31 -- * 12-JUL-2009 MDARBHA 120.0.12010000.6 8667163 Changed the lcu_org_hierarchy query to get all the organizations related to a Business Group
32 -- Added cursor lcu_org_hierarchy query_nn to get the organizations in case the Organization parameter is null.
33 --* 20-JUL-2009 MDARBHA 120.0.12010000.7 8675479 Changed the lcu_org_hierarchy query to get only the organizations from primary hierarchy
34 --
35 -- *********************************************************************************************************************************
36 AS
37 --
38 gb_debug BOOLEAN;
39 --
40 FUNCTION get_org_hirerachy(p_business_group_id IN per_assignments_f.business_group_id%TYPE
41 ,p_organization_id IN per_assignments_f.organization_id%TYPE
42 ,p_include_org_hierarchy IN VARCHAR2
43 )
44 RETURN gt_org_tbl
45 AS
46 --
47 -- bug # 8623767 - Modified the query to get all levels in hierarchy
48 -- bug # 8667163 -Modified to get all the organizations related to a Business Group
49 CURSOR lcu_org_hierarchy
50 IS
51 SELECT organization_id
52 FROM hr_all_organization_units
53 WHERE business_group_id=p_business_group_id
54 AND organization_id NOT IN(SELECT business_group_id
55 FROM hr_organization_units
56 WHERE business_group_id=p_business_group_id);
57 --
58 -- bug # 8667163 -Modified to get all the organizations related to a Organization Passed as a parameter.
59 CURSOR lcu_org_hierarchy_nn
60 IS
61 SELECT DISTINCT POSE.organization_id_child organization_id
62 FROM per_org_structure_elements POSE
63 ,per_organization_structures POS
64 ,per_org_structure_versions POSV
65 WHERE POSE.business_group_id = p_business_group_id
66 AND p_include_org_hierarchy = 'Y'
67 AND POSV.org_structure_version_id = POSE.org_structure_version_id
68 AND POS.primary_structure_flag='Y'
69 AND POS.organization_structure_id = POSV.organization_structure_id
70 START WITH POSE.organization_id_parent = p_organization_id
71 CONNECT BY PRIOR POSE.organization_id_child = POSE.organization_id_parent;
72 --
73 lt_org_id gt_org_tbl;
74 ln_index NUMBER := 0;
75 --
76 BEGIN
77 --
78 gb_debug := hr_utility.debug_enabled;
79 --
80 IF gb_debug THEN
81 hr_utility.set_location ('In Organization Hierarchy Package',10);
82 END IF;
83 --
84 IF p_business_group_id = p_organization_id THEN
85 --
86 IF gb_debug THEN
87 hr_utility.set_location ('Business_group_id=Organization_id',10);
88 END IF;
89 --
90 FOR lr_org_hierarchy IN lcu_org_hierarchy
91 LOOP
92 ln_index := ln_index + 1;
93 lt_org_id(ln_index) := lr_org_hierarchy.organization_id;
94 --
95 IF gb_debug THEN
96 hr_utility.set_location (lr_org_hierarchy.organization_id,10);
97 END IF;
98 --
99 END LOOP;
100 ELSE
101 FOR lr_org_hierarchy IN lcu_org_hierarchy_nn
102 LOOP
103 ln_index := ln_index + 1;
104 lt_org_id(ln_index) := lr_org_hierarchy.organization_id;
105 --
106 IF gb_debug THEN
107 hr_utility.set_location (lr_org_hierarchy.organization_id,10);
108 END IF;
109 --
110 END LOOP;
111 END IF;
112 --
113 ln_index := ln_index + 1;
114 lt_org_id(ln_index) := p_organization_id;
115 --
116 IF gb_debug THEN
117 hr_utility.set_location (p_organization_id,10);
118 END IF;
119 --
120 RETURN lt_org_id;
121 --
122 END get_org_hirerachy;
123
124 END per_jp_report_common_pkg;
[Home] [Help]