[Home] [Help]
PACKAGE BODY: APPS.PER_PERRPROH_XMLP_PKG
Source
1 PACKAGE BODY PER_PERRPROH_XMLP_PKG AS
2 /* $Header: PERRPROHB.pls 120.1 2007/12/06 11:33:03 amakrish noship $ */
3 function BeforeReport return boolean is
4 begin
5 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
6 declare
7
8 CURSOR csr_org_structure_version
9 (p_org_structure_version_id IN NUMBER)
10 IS
11 SELECT business_group_id
12 FROM per_org_structure_versions
13 WHERE org_structure_version_id = p_org_structure_version_id;
14
15 v_org_structure_name varchar2(30);
16 v_org_version number;
17 v_version_start_date date;
18 v_version_end_date date;
19 v_parent_org_name varchar2(240);
20 v_parent_org_type varchar2(90);
21 begin
22
23
24 --hr_standard.event('BEFORE REPORT');
25
26
27 OPEN csr_org_structure_version(p_org_structure_version_id);
28 FETCH csr_org_structure_version INTO p_business_group_id;
29 CLOSE csr_org_structure_version;
30 IF (p_business_group_id IS NOT NULL) THEN
31 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
32 ELSE
33 c_business_group_name := c_global_hierarchy;
34 END IF;
35
36
37 hr_reports.get_organization_hierarchy(null,
38 p_org_structure_version_id,
39 v_org_structure_name,
40 v_org_version,
41 v_version_start_date,
42 v_version_end_date);
43
44 c_org_hierarchy_name := v_org_structure_name;
45 c_version := v_org_version;
46 c_version_start_date := v_version_start_date;
47 c_version_end_date := v_version_end_date;
48
49 hr_reports.get_organization(p_parent_organization_id,
50 v_parent_org_name,
51 v_parent_org_type);
52
53 c_parent_org_name := v_parent_org_name;
54
55 if p_session_date >= c_version_start_date and
56 p_session_date <= nvl(c_version_end_date,
57 to_date('31/12/4712','DD/MM/YYYY')) then
58 c_session_date := p_session_date;
59 else
60 c_session_date := c_version_start_date;
61 end if;
62
63 c_managers_shown :=
64 hr_reports.get_lookup_meaning('YES_NO',
65 p_manager_flag);
66
67 end; return (TRUE);
68 end;
69
70 function c_nameformula(organization_id_parent in number) return varchar2 is
71 begin
72
73 declare
74 v_org_name varchar2(240);
75 v_org_type varchar2(90);
76 begin
77
78 hr_reports.get_organization(organization_id_parent,
79 v_org_name,
80 v_org_type);
81 c_type := v_org_type;
82
83 return v_org_name;
84
85 end;
86 RETURN NULL; end;
87
88 function c_count_org_subords2formula(organization_id_child in number) return number is
89 begin
90
91 return(hr_reports.count_org_subordinates(p_org_structure_version_id,organization_id_child));
92
93 end;
94
95 --function c_count_child_orgs1formula(organization_id_parent in number) return number is
96 function c_count_child_orgs1formula(arg_organization_id_parent in number) return number is
97 begin
98
99 declare
100 v_count_child_orgs number;
101 begin
102
103 begin
104 select count(*)
105 into v_count_child_orgs
106 from per_org_structure_elements ose
107 where ose.org_structure_version_id = p_org_structure_version_id
108 --and ose.organization_id_parent = organization_id_parent;
109 and ose.organization_id_parent = arg_organization_id_parent;
110 exception
111 when no_data_found then null;
112 end;
113
114 return v_count_child_orgs;
115
116 end;
117 RETURN NULL; end;
118
119 function c_count_managersformula(organization_id_parent in number) return varchar2 is
120 begin
121
122 declare
123
124 v_count_managers number;
125
126 begin
127
128 select count(*)
129 into v_count_managers
130 from per_people_f peo,
131 per_assignments_f asg
132 where peo.person_id = asg.person_id
133 and asg.assignment_type in ('E','C') and asg.manager_flag = 'Y'
134 and asg.organization_id = organization_id_parent
135 and c_session_date between asg.effective_start_date
136 and asg.effective_end_date
137 and c_session_date between peo.effective_start_date
138 and peo.effective_end_date;
139
140 return v_count_managers;
141
142 end;
143 RETURN NULL; end;
144
145 function c_count_managers1formula(organization_id_child in number) return number is
146 begin
147
148 declare
149
150 v_count_managers number;
151
152 begin
153
154 select count(*)
155 into v_count_managers
156 from per_people_f peo,
157 per_assignments_f asg
158 where peo.person_id = asg.person_id
159 and asg.assignment_type in ('E','C') and asg.manager_flag = 'Y'
160 and asg.organization_id = organization_id_child
161 and c_session_date between asg.effective_start_date
162 and asg.effective_end_date
163 and c_session_date between peo.effective_start_date
164 and peo.effective_end_date;
165
166 return v_count_managers;
167
168 end;
169 RETURN NULL; end;
170
171 function c_count_org_subordsformula(organization_id_parent in number) return number is
172 begin
173
174
175 return (hr_reports.count_org_subordinates(p_org_structure_version_id,
176 organization_id_parent));
177
178
179 end;
180
181 function AfterReport return boolean is
182 begin
183
184 --hr_standard.event('AFTER REPORT');
185
186 return (TRUE);
187 end;
188
189 --Functions to refer Oracle report placeholders--
190
191 Function C_type_p return varchar2 is
192 Begin
193 return C_type;
194 END;
195 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
196 Begin
197 return C_BUSINESS_GROUP_NAME;
198 END;
199 Function C_REPORT_SUBTITLE_p return varchar2 is
200 Begin
201 return C_REPORT_SUBTITLE;
202 END;
203 Function C_ORG_HIERARCHY_NAME_p return varchar2 is
204 Begin
205 return C_ORG_HIERARCHY_NAME;
206 END;
207 Function C_VERSION_p return number is
208 Begin
209 return C_VERSION;
210 END;
211 Function C_VERSION_START_DATE_p return date is
212 Begin
213 return C_VERSION_START_DATE;
214 END;
215 Function C_VERSION_END_DATE_p return date is
216 Begin
217 return C_VERSION_END_DATE;
218 END;
219 Function C_PARENT_ORG_NAME_p return varchar2 is
220 Begin
221 return C_PARENT_ORG_NAME;
222 END;
223 Function C_SESSION_DATE_p return varchar2 is
224 Begin
225 return C_SESSION_DATE;
226 END;
227 Function C_MANAGERS_SHOWN_p return varchar2 is
228 Begin
229 return C_MANAGERS_SHOWN;
230 END;
231 Function C_GLOBAL_HIERARCHY_p return varchar2 is
232 Begin
233 return C_GLOBAL_HIERARCHY;
234 END;
235 END PER_PERRPROH_XMLP_PKG ;