1 PACKAGE BODY CN_GET_SRP_DATA_PVT AS
2 /*$Header: cnvsfgtb.pls 115.18.115100.3 2004/05/11 01:33:08 sbadami ship $*/
3
4 PROCEDURE Get_Srp_List
5 (x_srp_data OUT NOCOPY srp_data_tbl_type) IS
6
7 CURSOR get_data IS
8 select srp_id, name, emp_num from cn_srp_hr_data order by name;
9
10 rownum NUMBER := 0;
11
12 BEGIN
13 for c in get_data loop
14 rownum := rownum + 1;
15 x_srp_data(rownum).srp_id := c.srp_id;
16 x_srp_data(rownum).name := c.name;
17 x_srp_data(rownum).emp_num := c.emp_num;
18 end loop;
19 END Get_Srp_List;
20
21 PROCEDURE Search_Srp_Data
22 (p_range_low IN NUMBER,
23 p_range_high IN NUMBER,
24 p_date IN DATE,
25 p_search_name IN VARCHAR2 := '%',
26 p_search_job IN VARCHAR2 := '%',
27 p_search_emp_num IN VARCHAR2 := '%',
28 p_search_group IN VARCHAR2 := '%',
29 p_order_by IN NUMBER := 1,
30 p_order_dir IN VARCHAR2 := 'ASC',
31 x_total_rows OUT NOCOPY NUMBER,
32 x_srp_data OUT NOCOPY srp_data_tbl_type) IS
33
34 TYPE rc IS ref cursor;
35
36 query VARCHAR2(4000) := '
37 select distinct
38 s.srp_id srp_id,
39 s.name name,
40 s.emp_num emp_num,
41 s.start_date start_date,
42 s.end_date end_date,
43 s.cost_center cost_center,
44 sg.group_id comp_group_id,
45 c.group_name comp_group_name,
46 j.job_id job_code,
47 j.name job_title,
48 srd.job_discretion disc_job_title,
49 -- sr.role_id role_id,
50 -- null role_name
51 1 role_id,
52 ''A'' role_name
53 from cn_srp_hr_data s,
54 cn_srp_role_dtls srd,
55 cn_srp_roles sr,
56 per_jobs j,
57 jtf_rs_group_members sg,
58 jtf_rs_groups_tl c,
59 jtf_rs_group_usages u,
60 jtf_rs_role_relations rr2
61 where s.srp_id = sr.salesrep_id
62 and sr.srp_role_id = srd.srp_role_id
63 and srd.job_title_id = j.job_id (+)
64 and s.resource_id = sg.resource_id
65 and sg.group_id = c.group_id
66 and c.language = userenv(''LANG'')
67 and u.group_id = c.group_id
68 and rr2.role_resource_id = sg.group_member_id
69 and rr2.role_resource_type = ''RS_GROUP_MEMBER''
70 and rr2.role_id = sr.role_id
71 and rr2.delete_flag = ''N''
72 and sg.delete_flag = ''N''
73 and u.usage = ''SF_PLANNING''';
74
75 cursor get_job_code (l_job_title_id in number) is
76 select job_code
77 from cn_job_titles
78 where job_title_id = l_job_title_id;
79
80 rec srp_data_rec_type;
81 l_index NUMBER := 0;
82 query_cur rc;
83 l_name VARCHAR2(241) := upper(p_search_name) || '%';
84 l_emp_num VARCHAR2(31) := upper(p_search_emp_num) || '%';
85
86 BEGIN
87 x_total_rows := 0;
88
89 if (p_search_name <> '%') then
90 query := query || ' and upper(s.name) like :1';
91 else
92 query := query || ' and :1 is not null'; -- dummy to get past :1
93 end if;
94
95 if (p_search_emp_num <> '%') then
96 query := query || ' and upper(s.emp_num) like :2';
97 else
98 query := query || ' and :2 is not null';
99 end if;
100
101 if (p_search_job <> '%') then
102 query := query || ' and j.job_id = :3';
103 else
104 query := query || ' and :3 is not null';
105 end if;
106
107 if (p_search_group <> '%') then
108 query := query || ' and c.group_id = :4';
109 else
110 query := query || ' and :4 is not null';
111 end if;
112
113 query := query || ' and :5 between rr2.start_date_active and' ||
114 ' nvl(rr2.end_date_active, :6)' || 'and (:7 between sr.start_date and nvl(sr.end_date,:8))';
115
116 -- order by clause
117 query := query || ' order by ' || p_order_by || ' ' || p_order_dir;
118
119 open query_cur for query using l_name, l_emp_num, p_search_job,
120 p_search_group, p_date, p_date,p_date,p_date;
121 loop
122 fetch query_cur into rec;
123 exit when query_cur%notfound;
124
125 x_total_rows := x_total_rows + 1;
126 if x_total_rows between p_range_low and p_range_high then
127 l_index := l_index + 1;
128 x_srp_data(l_index).srp_id := rec.srp_id;
129 x_srp_data(l_index).name := rec.name;
130 x_srp_data(l_index).emp_num := rec.emp_num;
131 x_srp_data(l_index).job_title := rec.job_title;
132 x_srp_data(l_index).disc_job_title := rec.disc_job_title;
133 x_srp_data(l_index).comp_group_id := rec.comp_group_id;
134 x_srp_data(l_index).comp_group_name:= rec.comp_group_name;
135
136 -- get job_code
137 open get_job_code(rec.job_code);
138 fetch get_job_code into x_srp_data(l_index).job_code;
139 close get_job_code;
140
141 end if;
142 end loop;
143 close query_cur;
144
145 END Search_Srp_Data;
146
147 PROCEDURE Get_Srp_Data
148 (p_srp_id IN NUMBER,
149 x_srp_data OUT NOCOPY srp_data_tbl_type) IS
150
151 CURSOR c is
152 select s.srp_id,
153 s.name,
154 s.emp_num,
155 s.start_date,
156 s.end_date,
157 s.cost_center,
158 null comp_group_name, null comp_group_id,
159 null job_code, null job_title, null disc_job_title,
160 null role_id, null role_name --recycle rec type
161 from cn_srp_hr_data s
162 where s.srp_id = p_srp_id;
163 BEGIN
164 open c;
165 fetch c into x_srp_data(1);
166 close c;
167 END;
168
169 PROCEDURE Get_Managers
170 (p_srp_id IN NUMBER,
171 p_date IN DATE,
172 p_comp_group_id IN NUMBER,
173 x_srp_data OUT NOCOPY srp_data_tbl_type) IS
174
175 g_end_of_time DATE := to_date('12/31/9999', 'MM/DD/YYYY');
176
177 -- working variables
178 l_fy_start_date DATE;
179 l_cg_start_date DATE;
180 l_cg_end_date DATE;
181 l_mgr_assign_start DATE;
182 l_mgr_assign_end DATE;
183 l_manager_flag VARCHAR2(1);
184 l_rownum NUMBER := 0;
185
186 cursor get_fy_start_date is
187 select min(p2.start_date)
188 from cn_period_statuses p1,
189 cn_period_statuses p2
190 where p_date between p1.start_date and p1.end_date
191 and p1.period_year = p2.period_year;
192
193 cursor get_srp_group_info is
194 select manager_flag, start_date_active,
195 nvl(end_date_active, g_end_of_time) end_date_active
196 from cn_qm_mgr_srp_groups
197 where srp_id = p_srp_id and comp_group_id = p_comp_group_id;
198
199 cursor get_group_members (l_comp_group_id in number) is
200 select sg.role_id, sg.role_name, sg.start_date_active, sg.group_name,
201 nvl(sg.end_date_active, g_end_of_time) end_date_active,
202 sg.srp_id, sd.name srp_name, sd.emp_num, sg.manager_flag,
203 decode(sg.manager_flag, 'Y', sd.name || ' *', sd.name) ast_name
204 from cn_qm_mgr_srp_groups sg, cn_srp_hr_data sd
205 where sg.comp_group_id = l_comp_group_id and sg.srp_id = sd.srp_id;
206
207 cursor get_parent_groups is
208 select parent_comp_group_id, start_date_active,
209 nvl(end_date_active, g_end_of_time) end_date_active
210 from cn_qm_group_hier
211 where comp_group_id = p_comp_group_id;
212
213 BEGIN
214 -- get information of current srp comp_group assignment
215 open get_srp_group_info;
216 fetch get_srp_group_info into l_manager_flag, l_cg_start_date,l_cg_end_date;
217 close get_srp_group_info;
218
219 -- get date range we are interested in for this query
220 -- (beginning of current FY until p_date)
221 open get_fy_start_date;
222 fetch get_fy_start_date into l_fy_start_date;
223 close get_fy_start_date;
224
225 -- if the current srp_id is a salesrep in its group, add its own
226 -- group's manager to mgr list if it exists. if it exists, indicate
227 -- with (*) and don't fetch higher managers
228 if l_manager_flag = 'N' then
229 for pgm in get_group_members(p_comp_group_id) loop
230 -- make sure this group_member is a manager
231 if pgm.manager_flag = 'Y' then
232 -- check date range intersections
233 -- we want (current group assign dates) int
234 -- (parent member assign dates)
235 l_mgr_assign_start := greatest(l_cg_start_date,
236 pgm.start_date_active);
237 l_mgr_assign_end := least(l_cg_end_date,
238 pgm.end_date_active);
239 -- if date range exists (start <= end) and it intersects current
240 -- date interval then insert into result tbl
241 if (l_mgr_assign_start <= l_mgr_assign_end AND
242 greatest(l_mgr_assign_start, l_fy_start_date) <=
243 least (l_mgr_assign_end, p_date)) then
244 -- realize that g_end_of_time is really null
245 if l_mgr_assign_end = g_end_of_time then
246 l_mgr_assign_end := null;
247 end if;
248 l_rownum := l_rownum + 1;
249 x_srp_data(l_rownum).srp_id := pgm.srp_id;
250 x_srp_data(l_rownum).name := pgm.ast_name;
251 x_srp_data(l_rownum).emp_num := pgm.emp_num;
252 x_srp_data(l_rownum).start_date := l_mgr_assign_start;
253 x_srp_data(l_rownum).end_date := l_mgr_assign_end;
254 x_srp_data(l_rownum).comp_group_id := p_comp_group_id;
255 x_srp_data(l_rownum).comp_group_name := pgm.group_name;
256 x_srp_data(l_rownum).role_id := pgm.role_id;
257 x_srp_data(l_rownum).role_name := pgm.role_name;
258 end if;
259 end if;
260 end loop;
261 end if;
262
263 -- get info about members of parent groups and add it to result table
264 -- if salesrep doesn't already have a manger
265 if l_rownum = 0 then
266 for pg in get_parent_groups loop
267 -- for each parent group, get its members
268 for pgm in get_group_members(pg.parent_comp_group_id) loop
269 -- check date range intersections
270 -- we want (current group assign dates) int
271 -- (dates on hierarchy edge) int (parent group assign dates)
272 l_mgr_assign_start := greatest(l_cg_start_date,
273 pg.start_date_active,
274 pgm.start_date_active);
275 l_mgr_assign_end := least(l_cg_end_date,
276 pg.end_date_active,
277 pgm.end_date_active);
278 -- if date range exists (start <= end) and it intersects current
279 -- date interval then insert into result tbl
280 if (l_mgr_assign_start <= l_mgr_assign_end AND
281 greatest(l_mgr_assign_start, l_fy_start_date) <=
282 least (l_mgr_assign_end, p_date)) then
283 -- realize that g_end_of_time is really null
284 if l_mgr_assign_end = g_end_of_time then
285 l_mgr_assign_end := null;
286 end if;
287 l_rownum := l_rownum + 1;
288 x_srp_data(l_rownum).srp_id := pgm.srp_id;
289 x_srp_data(l_rownum).name := pgm.ast_name;
290 x_srp_data(l_rownum).emp_num := pgm.emp_num;
291 x_srp_data(l_rownum).start_date := l_mgr_assign_start;
292 x_srp_data(l_rownum).end_date := l_mgr_assign_end;
293 x_srp_data(l_rownum).comp_group_id := pg.parent_comp_group_id;
294 x_srp_data(l_rownum).comp_group_name := pgm.group_name;
295 x_srp_data(l_rownum).role_id := pgm.role_id;
296 x_srp_data(l_rownum).role_name := pgm.role_name;
297 end if;
298 end loop;
299 end loop;
300 end if;
301
302 END Get_Managers;
303
304 END CN_GET_SRP_DATA_PVT;
305
[Home] [Help]