1 Package Body hxc_approval_report_pkg as
2 /* $Header: hxcaprrp.pkb 115.5 2002/12/19 10:57:19 vsuriana ship $ */
3 -- get_employee_name
4 --
5 -- procedure
6 -- Brings back the employee name for a given person id
7 -- description
8 --
9 -- parameters
10 -- p_person_id - person Id
11 --
12 function get_employee_name(
13 p_person_id in varchar2
14 ) return varchar2
15 is
16 l_person_name varchar2(30);
17
18 begin
19 l_person_name := null;
20
21 if ( p_person_id is not null) then
22
23 -- Perf Rep - SQL ID: 3168839
24 -- select full_name into l_person_name
25 -- from per_all_people_f
26 -- where to_char(person_id) = p_person_id;
27
28 select full_name into l_person_name
29 from per_all_people_f
30 where person_id = to_number(p_person_id);
31
32 end if;
33
34 return l_person_name;
35
36 end get_employee_name;
37
38 -- get_last_aprv
39 --
40 -- procedure
41 -- Brings back the last approver for a given Approval timecard ID and OVN
42 -- parameters
43 -- p_timecard_id - Application Period Building Block Id
44 -- p_timecard_ovn - Application Period Building Block Ovn
45 --
46
47
48 function get_last_aprv(
49 p_timecard_id in number,
50 p_timecard_ovn in number)
51 return varchar2
52 is
53 l_last_approver varchar2(30);
54 l_last_approver_id number;
55 begin
56
57 l_last_approver := null;
58
59 --
60 -- Selects the last approver Time Attribute Id
61 --
62 select max(htau_last_aprv.time_attribute_id ) INTO l_last_approver_id
63 from
64 hxc_time_building_blocks htb,
65 hxc_time_attribute_usages htau_last_aprv,
66 hxc_time_attributes hta_last_aprv
67 where
68 htb.time_building_block_id = p_timecard_id
69 and htb.object_version_number = p_timecard_ovn
70 and htb.time_building_block_id = htau_last_aprv.time_building_block_id
71 and htb.object_version_number = htau_last_aprv.time_building_block_ovn
72 and htau_last_aprv.time_attribute_id = hta_last_aprv.time_attribute_id
73 and hta_last_aprv.attribute_category = 'APPROVAL'
74 and hta_last_aprv.attribute4 = 'FINISHED' ;
75
76 if (l_last_approver_id is not null)
77 then
78 select attribute3 into l_last_approver from hxc_time_attributes
79 where time_attribute_id = l_last_approver_id ;
80 end if;
81
82 return l_last_approver;
83
84 end get_last_aprv;
85
86 -- get_project_name
87 --
88 -- procedure
89 -- Brings back the project name for a given project number
90 -- description
91 --
92 -- parameters
93 -- p_project_number - project number
94 --
95
96 function get_project_name(
97 p_project_number in varchar2
98 ) return varchar2
99 is
100 l_project_name varchar2(100);
101 begin
102 l_project_name := null;
103
104
105 if (p_project_number is not null)
106 then
107 select project_name into l_project_name
108 from pa_online_projects_v
109 where project_number = p_project_number;
110 end if;
111 return l_project_name;
112 end get_project_name;
113
114 -- get_task_name
115 --
116 -- procedure
117 -- Brings back the task name for a given task number
118 -- description
119 --
120 -- parameters
121 -- p_task_number - Task number
122 --
123 function get_task_name(
124 p_task_number in varchar2
125 ) return varchar2
126 is
127 l_task_name varchar2(100);
128 begin
129 l_task_name := null;
130
131
132 if (p_task_number is not null)
133 then
134 select task_name into l_task_name
135 from pa_online_tasks_v
136 where task_number = p_task_number;
137 end if;
138 return l_task_name;
139 end get_task_name;
140
141 -- get_element_name
142 --
143 -- procedure
144 -- Brings back the element name for a given element type id
145 --
146 -- parameters
147 -- p_element_type_id - element type id
148 --
149 -- returns
150 -- varchar2 - element name
151
152 function get_element_name(
153 p_element_type_id varchar2) return varchar2
154 is
155 l_element_name varchar2(100);
156 begin
157 l_element_name := null;
158 if(p_element_type_id is not null )
159 then
160 -- Perf Rep - SQL ID:3168908
161 -- select pet.element_name into l_element_name
162 -- from pay_element_types_f pet
163 -- where to_char(pet.element_type_id) = p_element_type_id;
164
165 select pet.element_name into l_element_name
166 from pay_element_types_f pet
167 where pet.element_type_id = to_number(p_element_type_id);
168
169 end if;
170 return l_element_name;
171 end get_element_name;
172
173 -- get_application_name
174 --
175 -- procedure
176 -- Brings back the application name for a given time recipient id
177 --
178 -- parameters
179 -- p_time_recipient_id - Time Recipient Id
180 --
181 -- returns
182 -- varchar2 - application Name
183
184
185 function get_application_name(
186 p_time_recipient_id varchar2) return varchar2
187 is
188 l_application_name varchar2(100);
189 begin
190 l_application_name := null;
191 if(p_time_recipient_id is not null )
192 then
193 select fav.application_name into l_application_name
194 from hxc_time_recipients htr,fnd_application_vl fav
195 where
196 to_char(htr.time_recipient_id) = p_time_recipient_id
197 and fav.application_id = htr.application_id;
198 end if;
199 return l_application_name;
200
201 end get_application_name;
202
203 -- get_supervisor_name
204 --
205 -- procedure
206 -- Brings back the supervior for a given person
207 --
208 -- parameters
209 -- p_person_id - person id
210 --
211 -- returns
212 -- varchar2 - supervisor full name
213
214
215 function get_supervisor_name(
216 p_person_id in number
217 ) return varchar2
218 is
219 l_person_name varchar2(30);
220 l_supervisor_id number;
221 begin
222 l_supervisor_id :=null;
223 l_person_name := null;
224
225 if ( p_person_id is not null) then
226 select supervisor_id into l_supervisor_id
227 from per_assignments_f
228 where person_id = p_person_id;
229
230 if (l_supervisor_id is not null) then
231
232 select full_name into l_person_name
233 from per_all_people_f
234 where person_id = l_supervisor_id ;
235 end if;
236 end if;
237
238 return l_person_name;
239
240 end get_supervisor_name;
241
242 -- get_organization_name
243 --
244 -- procedure
245 -- Brings back the organization name for a given person
246 --
247 -- parameters
248 -- p_person_id - person id
249 --
250 -- returns
251 -- varchar2 - organization name
252
253 function get_organization_name(
254 p_person_id in number
255 ) return varchar2
256 is
257 l_organization_name varchar2(240);
258 l_organization_id number;
259 begin
260 l_organization_name :=null;
261 l_organization_id := null;
262
263 if ( p_person_id is not null) then
264 select organization_id into l_organization_id
265 from per_assignments_f
266 where person_id = p_person_id;
267
268 if (l_organization_id is not null) then
269 select name into l_organization_name
270 from per_organization_units
271 where organization_id = l_organization_id ;
272 end if;
273 end if;
274
275 return l_organization_name;
276 end get_organization_name;
277
278 -- get_cost_center
279 --
280 -- procedure
281 -- Brings back the cost center for a given person
282 --
283 -- parameters
284 -- p_person_id - person id
285 --
286 -- returns
287 -- varchar2 - cost center
288
289 function get_cost_center(
290 p_person_id in number
291 ) return varchar2
292 is
293 l_cost_center varchar2(30);
294 l_flex_id number;
295 begin
296 l_cost_center :=null;
297 l_flex_id := null;
298
299 if ( p_person_id is not null) then
300 select soft_coding_keyflex_id into l_flex_id
301 from per_assignments_f
302 where person_id = p_person_id;
303
304 if (l_flex_id is not null) then
305 select CONCATENATED_SEGMENTS into l_cost_center
306 from pay_cost_allocation_keyflex
307 where cost_allocation_keyflex_id = l_flex_id ;
308 end if;
309 end if;
310
311 return l_cost_center;
312
313 end get_cost_center;
314
315 function get_payroll_name(
316 p_person_id in number
317 ) return varchar2
318 is
319 l_payroll_name varchar2(30);
320 l_payroll_id number;
321 begin
322 l_payroll_name :=null;
323 l_payroll_id := null;
324
325 if ( p_person_id is not null) then
326 select payroll_id into l_payroll_id
327 from per_assignments_f
328 where person_id = p_person_id;
329
330 if (l_payroll_id is not null) then
331 select payroll_name into l_payroll_name
332 from pay_all_payrolls_f
333 where payroll_id= l_payroll_id ;
334 end if;
335 end if;
336
337 return l_payroll_name;
338
339 end get_payroll_name;
340
341 function get_business_group_name(
342 p_person_id in number
343 ) return varchar2
344 is
345 l_bg_name varchar2(240);
346 l_bg_id number;
347 begin
348 l_bg_name :=null;
349 l_bg_id := null;
350
351 if ( p_person_id is not null) then
352 select business_group_id into l_bg_id
353 from per_all_people_f
354 where person_id = p_person_id;
355
356 if (l_bg_id is not null) then
357 select name into l_bg_name
358 from per_business_groups
359 where business_group_id = l_bg_id;
360 end if;
361
362 end if;
363
364 return l_bg_name;
365
366 end get_business_group_name;
367
368
369 end hxc_approval_report_pkg;
[Home] [Help]