[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SKILLS_REPORT_PUB
Source
1 PACKAGE BODY jtf_rs_skills_report_pub AS
2 /* $Header: jtfrsbsb.pls 120.0 2005/05/11 08:19:25 appldev ship $ */
3
4 /*****************************************************************************************
5 ******************************************************************************************/
6
7 /* Package variables. */
8
9 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_SKILLS_REPORT_PUB';
10
11 PROCEDURE show_res_skills (
12 ERRBUF OUT NOCOPY VARCHAR2,
13 RETCODE OUT NOCOPY VARCHAR2,
14 X_REPORT_TYPE IN VARCHAR2,
15 X_RESOURCE_ID IN NUMBER,
16 X_GROUP_ID IN NUMBER
17 ) IS
18
19 cursor c_grp_name (l_group_id jtf_rs_groups_b.group_id%type) is
20 select group_name
21 from jtf_rs_groups_vl
22 where group_id = l_group_id;
23
24 cursor c_grp_mem (l_group_id jtf_rs_groups_b.group_id%type) is
25 select resource_id
26 from jtf_rs_group_members
27 where group_id = l_group_id;
28
29 c_grp_mem_rec c_grp_mem%rowtype;
30
31 l_report_type varchar2(30) := x_report_type;
32 l_resource_id jtf_rs_resource_extns.resource_id%type := x_resource_id;
33 l_group_id jtf_rs_groups_b.group_id%type := x_group_id;
34
35 m_resource_id jtf_rs_resource_extns.resource_id%type;
36 p_resource_id jtf_rs_resource_extns.resource_id%type;
37 l_res_mgr_name jtf_rs_resource_extns_vl.resource_name%type;
38 l_group_name jtf_rs_groups_vl.group_name%type;
39 l_level_id number;
40 p_level number;
41
42 cursor c_res_hier (l_resource_id jtf_rs_resource_extns.resource_id%type) is
43 select resource_id ,level
44 from jtf_rs_resource_extns
45 where level > 1
46 connect by source_mgr_id = prior source_id
47 start with resource_id = l_resource_id;
48
49 c_res_hier_rec c_res_hier%rowtype;
50
51 cursor c_res_mgr_name (l_resource_id jtf_rs_resource_extns.resource_id%type) is
52 select resource_name
53 from jtf_rs_resource_extns_vl
54 where resource_id = l_resource_id;
55
56 BEGIN
57
58 if l_report_type = 'RESOURCE' then
59 open c_res_mgr_name (l_resource_id);
60 fetch c_res_mgr_name into l_res_mgr_name;
61 close c_res_mgr_name;
62
63 fnd_file.new_line(fnd_file.log,1);
64 fnd_file.put_line(fnd_file.log,'Skills Report of HR Hierachy, for a Resource');
65 fnd_file.put_line(fnd_file.log,'--------------------------------------------');
66 fnd_file.new_line(fnd_file.log,1);
67 fnd_file.put_line(fnd_file.log,'Manager : '||l_res_mgr_name);
68 fnd_file.put_line(fnd_file.log,'Group : HR Hierarchy');
69 fnd_file.new_line(fnd_file.log,1);
70
71 open c_res_hier (l_resource_id);
72 fetch c_res_hier into c_res_hier_rec;
73 if c_res_hier%notfound then
74 fnd_file.put_line(fnd_file.log,'This Manager (Resource) does not have any direct reports');
75 fnd_file.new_line(fnd_file.log,1);
76 else
77 fnd_file.put_line(fnd_file.log,'Resource Name '||
78 'Number '||'Skill Type '||
79 'Skill Name '||'Level ');
80 fnd_file.put_line(fnd_file.log,'-------------------------------------------------- '||
81 '--------------- '||'-------------------- '||
82 '-------------------------------------------------- '||'-------------------- ');
83 fnd_file.new_line(fnd_file.log,1);
84 while (c_res_hier%found) loop
85 p_resource_id := c_res_hier_rec.resource_id;
89 end loop;
86 p_level := c_res_hier_rec.level;
87 get_res_skills (p_resource_id,p_level);
88 fetch c_res_hier into c_res_hier_rec;
90 end if;
91 close c_res_hier;
92 elsif l_report_type = 'GROUP' then
93 open c_grp_name(l_group_id);
94 fetch c_grp_name into l_group_name;
95 close c_grp_name;
96
97 fnd_file.new_line(fnd_file.log,1);
98 fnd_file.put_line(fnd_file.log,'Skills Report of all Members, for a Group');
99 fnd_file.put_line(fnd_file.log,'-----------------------------------------');
100 fnd_file.new_line(fnd_file.log,1);
101 fnd_file.put_line(fnd_file.log,'Group Name : '||l_group_name);
102 fnd_file.new_line(fnd_file.log,1);
103
104 open c_grp_mem(l_group_id);
105 fetch c_grp_mem into c_grp_mem_rec;
106 if c_grp_mem%notfound then
107 fnd_file.put_line(fnd_file.log,'This Group does not have any members');
108 fnd_file.new_line(fnd_file.log,1);
109 else
110 fnd_file.put_line(fnd_file.log,'Resource Name '||
111 'Number '||'Skill Type '||
112 'Skill Name '||'Level ');
113 fnd_file.put_line(fnd_file.log,'-------------------------------------------------- '||
114 '--------------- '||'-------------------- '||
115 '-------------------------------------------------- '||'-------------------- ');
116 fnd_file.new_line(fnd_file.log,1);
117 while (c_grp_mem%found) loop
118 m_resource_id := c_grp_mem_rec.resource_id;
119 get_res_skills (m_resource_id,2);
120 fetch c_grp_mem into c_grp_mem_rec;
121 end loop;
122 end if;
123 close c_grp_mem;
124 end if;
125
126 END show_res_skills;
127
128 PROCEDURE get_res_skills (
129 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE
130 ,P_LEVEL IN NUMBER
131 ) IS
132
133 cursor c_res_skills is
134 select fnl.meaning skill_type, rsl.level_name, rsk.category_id, rsk.component_id,
135 rsk.subcategory, rsk.problem_code, rsk.product_id, rsk.platform_id
136 from fnd_lookups fnl, jtf_rs_resource_skills rsk,
137 jtf_rs_skill_levels_vl rsl
138 where lookup_type = 'JTF_RS_SKILL_CAT_TYPE'
139 and rsk.subcategory = lookup_code
140 and rsk.skill_level_id = rsl.skill_level_id
141 and rsk.resource_id = p_resource_id;
142
143 c_res_skills_rec c_res_skills%rowtype;
144
145 cursor c_res_name is
146 select resource_name, resource_number
147 from jtf_rs_resource_extns_vl
148 where resource_id = p_resource_id;
149
150 cursor c_prob_name (l_problem_code jtf_rs_problem_codes_v.problem_name%type) is
151 select problem_name
152 from jtf_rs_problem_codes_v
153 where problem_code = l_problem_code;
154
155 cursor c_plat_name (l_platform_id jtf_rs_products_v.product_name%type) is
156 select platform_name
157 from jtf_rs_platforms_v
158 where platform_id = l_platform_id;
159
160 cursor c_prod_name (l_product_id jtf_rs_products_v.product_name%type) is
161 select product_name
162 from jtf_rs_products_v
163 where product_id = l_product_id;
164
165 cursor c_cat_name (l_category_id jtf_rs_item_categories_v.category_name%type) is
166 select category_name
167 from jtf_rs_item_categories_v
168 where category_id = l_category_id;
169
170 cursor c_comp_name (l_component_id jtf_rs_components_v.component_name%type) is
171 select component_name
172 from jtf_rs_components_v
173 where component_id = l_component_id;
174
175 l_subcategory jtf_rs_resource_skills.subcategory%type;
176 l_problem_code jtf_rs_resource_skills.problem_code%type;
177 l_platform_id jtf_rs_resource_skills.platform_id%type;
178 l_product_id jtf_rs_resource_skills.product_id%type;
179
180 l_component_id jtf_rs_resource_skills.component_id%type := null;
181 l_category_id jtf_rs_resource_skills.category_id%type := null;
182 l_category_name jtf_rs_item_categories_v.category_name%type := null;
183 l_component_name jtf_rs_components_v.component_name%type := null;
184
185 l_skill_type fnd_lookups.meaning%type := null;
186 l_skill_name varchar2(242) := null;
187 l_skill_level jtf_rs_skill_levels_vl.level_name%type := null;
188
189 l_resource_number jtf_rs_resource_extns.resource_number%type;
190 l_resource_name jtf_rs_resource_extns_vl.resource_name%type;
191 l_res_name jtf_rs_resource_extns_vl.resource_name%type;
192
193 l_level number := p_level;
194 l_padder varchar(200):='';
195
196 BEGIN
197
198 if l_level > 2 then
199 for j in 3..l_level loop
200 l_padder := l_padder || '---';
201 end loop;
202 end if;
203
204 open c_res_name;
205 fetch c_res_name into l_res_name, l_resource_number;
206 close c_res_name;
207
208 l_resource_name := l_padder||l_res_name;
209
210 open c_res_skills;
211 fetch c_res_skills into c_res_skills_rec;
212 if c_res_skills%notfound then
213 fnd_file.put_line(fnd_file.log,rpad(l_resource_name,50,' ')||' '||rpad(l_resource_number,15,' ')||' '||
214 rpad(l_skill_type,20,' ') ||' '|| rpad(l_skill_name,50,' ')||' '||
215 rpad(l_skill_level,20,' '));
216 else
217 l_skill_type := '-';
218 l_skill_name := '-';
219 l_skill_level := '-';
220 while (c_res_skills%found) loop
221 l_subcategory := c_res_skills_rec.subcategory;
222 l_problem_code := c_res_skills_rec.problem_code;
223 l_platform_id := c_res_skills_rec.platform_id;
224 l_product_id := c_res_skills_rec.product_id;
225 l_skill_type := c_res_skills_rec.skill_type;
226 l_skill_level := c_res_skills_rec.level_name;
227 l_category_id := c_res_skills_rec.category_id;
228 l_component_id := c_res_skills_rec.component_id;
229
230 if l_subcategory = 'PROBLEM_CODE' then
231 open c_prob_name (l_problem_code);
232 fetch c_prob_name into l_skill_name;
233 close c_prob_name;
234 elsif l_subcategory = 'PLATFORM' then
235 open c_plat_name (l_platform_id);
236 fetch c_plat_name into l_skill_name;
237 close c_plat_name;
238 elsif l_subcategory = 'PRODUCT' then
239 open c_prod_name (l_product_id);
240 fetch c_prod_name into l_skill_name;
241 close c_prod_name;
242 if l_component_id is not null then
243 open c_comp_name(l_component_id);
244 fetch c_comp_name into l_component_name;
245 if c_comp_name%found then
246 if l_component_name is not null then
247 l_skill_name := l_skill_name||' -> '||l_component_name;
248 end if;
249 end if;
250 close c_comp_name;
251 end if;
252 end if;
253
254 if l_category_id is not null then
255 open c_cat_name(l_category_id);
256 fetch c_cat_name into l_category_name;
257 if c_cat_name%found then
258 if l_category_name is not null then
259 l_skill_name := l_category_name||' -> '||l_skill_name;
260 end if;
261 end if;
262 close c_cat_name;
263 end if;
264
265 fnd_file.put_line(fnd_file.log,rpad(l_resource_name,50,' ')||' '||rpad(l_resource_number,15,' ')||' '||
266 rpad(l_skill_type,20,' ') ||' '|| rpad(l_skill_name,50,' ')||' '||
267 rpad(l_skill_level,20,' '));
268 l_skill_type := '-';
269 l_skill_name := '-';
270 l_skill_level := '-';
271 fetch c_res_skills into c_res_skills_rec;
272 end loop;
273 end if;
274 close c_res_skills;
275 l_padder:='';
276 END get_res_skills;
277
278 END jtf_rs_skills_report_pub;