[Home] [Help]
PACKAGE BODY: APPS.PER_PERRPRPH_XMLP_PKG
Source
1 PACKAGE BODY PER_PERRPRPH_XMLP_PKG AS
2 /* $Header: PERRPRPHB.pls 120.1 2007/12/06 11:33:23 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6 P_SESSION_DATE1:=TO_CHAR(P_SESSION_DATE,'DD-MON-YYYY');
7 declare
8 v_pos_hierarchy_name varchar2(30);
9 v_version number;
10 v_version_start_date date;
11 v_version_end_date date;
12 begin
13
14
15
16 --hr_standard.event('BEFORE REPORT');
17
18 c_business_group_name :=
19 hr_reports.get_business_group(p_business_group_id);
20
21 hr_reports.get_position_hierarchy(null,
22 p_pos_structure_version_id,
23 v_pos_hierarchy_name,
24 v_version,
25 v_version_start_date,
26 v_version_end_date);
27
28 c_pos_hierarchy_name := v_pos_hierarchy_name;
29 c_version := v_version;
30 c_version_start_date := v_version_start_date;
31 c_version_end_date := v_version_end_date;
32
33 if p_session_date <= nvl(c_version_end_date,
34 to_date('31/12/4712','DD/MM/YYYY')) and
35 p_session_date >= c_version_start_date then
36 c_session_date := p_session_date;
37 else
38 c_session_date := c_version_start_date;
39 end if;
40
41
42 c_parent_position_name :=
43 hr_reports.get_position(p_parent_position_id, c_session_date);
44
45
46 c_holders_shown :=
47 hr_reports.get_lookup_meaning('YES_NO',
48 p_holder_flag);
49
50 end; return (TRUE);
51 end;
52
53 function c_nameformula(parent_position_id in number) return varchar2 is
54 begin
55
56 begin
57
58 return (hr_reports.get_position(parent_position_id,c_session_date));
59
60 end;
61 RETURN NULL; end;
62
63 function c_count_subordsformula(parent_position_id in number) return number is
64 begin
65
66 return (hr_reports.count_pos_subordinates(p_pos_structure_version_id,
67 parent_position_id));
68
69 end;
70
71 function c_count_subords1formula(subordinate_position_id in number) return number is
72 begin
73
74 return(hr_reports.count_pos_subordinates(p_pos_structure_version_id,
75 subordinate_position_id));
76
77
78 end;
79
80 --function c_count_child_posformula(parent_position_id in number) return number is
81 function c_count_child_posformula(arg_parent_position_id in number) return number is
82 begin
83
84 declare
85 v_count_child_pos number;
86 begin
87
88 select nvl(count(*),0)
89 into v_count_child_pos
90 from per_pos_structure_elements pse
91 where pse.pos_structure_version_id = p_pos_structure_version_id
92 --and pse.parent_position_id = parent_position_id;
93 and pse.parent_position_id = arg_parent_position_id;
94
95 return v_count_child_pos;
96
97 end;
98 RETURN NULL; end;
99
100 function c_count_holdersformula(parent_position_id in number) return number is
101 begin
102
103 declare
104
105 v_count_holders number;
106
107 begin
108
109 select count(*)
110 into v_count_holders
111 from per_people_f peo,
112 per_assignments_f asg
113 where peo.person_id = asg.person_id
114 and asg.position_id = parent_position_id
115 and c_session_date between peo.effective_start_date
116 and peo.effective_end_date
117 and c_session_date between asg.effective_start_date
118 and asg.effective_end_date
119 and peo.current_employee_flag = 'Y'
120 and asg.assignment_type <> 'A';
121
122 return v_count_holders;
123
124 end;
125 RETURN NULL; end;
126
127 function c_count_holders1formula(subordinate_position_id in number) return number is
128 begin
129
130 declare
131
132 v_count_holders number;
133
134 begin
135
136 select count(*)
137 into v_count_holders
138 from per_people_f peo,
139 per_assignments_f asg
140 where peo.person_id = asg.person_id
141 and asg.position_id = subordinate_position_id
142 and c_session_date between peo.effective_start_date
143 and peo.effective_end_date
144 and c_session_date between asg.effective_start_date
145 and asg.effective_end_date
146 and peo.current_employee_flag = 'Y'
147 and asg.assignment_type <> 'A';
148
149 return v_count_holders;
150
151 end;
152 RETURN NULL; end;
153
154 function AfterReport return boolean is
155 begin
156
157 --hr_standard.event('AFTER REPORT');
158
159 return (TRUE);
160 end;
161
162 --Functions to refer Oracle report placeholders--
163
164 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
165 Begin
166 return C_BUSINESS_GROUP_NAME;
167 END;
168 Function C_REPORT_SUBTITLE_p return varchar2 is
169 Begin
170 return C_REPORT_SUBTITLE;
171 END;
172 Function C_POS_HIERARCHY_NAME_p return varchar2 is
173 Begin
174 return C_POS_HIERARCHY_NAME;
175 END;
176 Function C_VERSION_p return number is
177 Begin
178 return C_VERSION;
179 END;
180 Function C_VERSION_START_DATE_p return date is
181 Begin
182 return C_VERSION_START_DATE;
183 END;
184 Function C_VERSION_END_DATE_p return date is
185 Begin
186 return C_VERSION_END_DATE;
187 END;
188 Function C_PARENT_POSITION_NAME_p return varchar2 is
189 Begin
190 return C_PARENT_POSITION_NAME;
191 END;
192 Function C_HOLDERS_SHOWN_p return varchar2 is
193 Begin
194 return C_HOLDERS_SHOWN;
195 END;
196 Function C_SESSION_DATE_p return date is
197 Begin
198 return C_SESSION_DATE;
199 END;
200 END PER_PERRPRPH_XMLP_PKG ;